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!
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.
0 Comments