What is SQL GROUP BY Clause?

 When working in SQL, results can be grouped according to a specific field or fields.

SQL: GROUP BY Clause

When working in SQL, results can be grouped according to a specific field or fields.

- GROUP BY must be placed immediately after the WHERE conditions, if any, and just before the ORDER BY clause.

- GROUP BY is one of the most powerful and useful tools in SQL

Syntax

The syntax for the GROUP BY clause in SQL is:

SELECT expression1, expression2, ... expression_n, 
       aggregate_function (aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n
[ORDER BY expression [ ASC | DESC ]];

Note:

• In most cases, when you need an aggregate function, you must add a GROUP BY clause in your query, too.
• Always include the field you have grouped your results by in the SELECT statement!

Example - Using GROUP BY

employee_number	last_name	first_name	salary	dept_id
1001	Smith	John	62000	500
1002	Anderson	Jane	57500	500
1003	Everest	Brad	71000	501
1004	Horvath	Jack	42000	501

Enter the following SQL statement:

SELECT dept_id, SUM(salary) AS total_salaries
FROM employees
GROUP BY dept_id;

There will be 2 records selected. These are the results that you should see:

dept_id	total_salaries
500	119500
501	113000

In this example, we've used the SUM function to add up all of the salaries for each dept_id and we've aliased the results of the SUM function as total_salaries. Because the dept_id is not encapsulated in the SUM function, it must be listed in the GROUP BY clause.