SQL: GROUP BY Clause
Mục Lục
SQL:
GROUP BY Clause
This SQL tutorial explains how to use the SQL GROUP BY clause with syntax and examples.
Description
The SQL GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns.
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 ]];
Parameters or Arguments
- expression1, expression2, … expression_n
- Expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY Clause at the end of the SQL statement.
- aggregate_function
- This is an aggregate function such as the SUM, COUNT, MIN, MAX, or AVG functions.
- aggregate_expression
- This is the column or expression that the aggregate_function will be used on.
- tables
- The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.
- WHERE conditions
- Optional. These are conditions that must be met for the records to be selected.
- ORDER BY expression
- Optional. The expression used to sort the records in the result set. If more than one expression is provided, the values should be comma separated.
- ASC
- Optional. ASC sorts the result set in ascending order by expression. This is the default behavior, if no modifier is provider.
- DESC
- Optional. DESC sorts the result set in descending order by expression.
DDL/DML for Examples
If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!
Get DDL/DML
Example – Using GROUP BY with the SUM Function
Let’s look at how to use the GROUP BY clause with the SUM function in SQL.
In this example, we have a table called employees with the following data:
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:
Try It
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.
Example – Using GROUP BY with the COUNT function
Let’s look at how to use the GROUP BY clause with the COUNT function in SQL.
In this example, we have a table called products with the following data:
product_id
product_name
category_id
1
Pear
50
2
Banana
50
3
Orange
50
4
Apple
50
5
Bread
75
6
Sliced Ham
25
7
Kleenex
NULL
Enter the following SQL statement:
Try It
SELECT category_id, COUNT(*) AS total_products FROM products WHERE category_id IS NOT NULL GROUP BY category_id ORDER BY category_id;
There will be 3 records selected. These are the results that you should see:
category_id
total_products
25
1
50
4
75
1
In this example, we’ve used the COUNT function to calculate the number of products for each category_id and we’ve aliased the results of the COUNT function as total_products. We’ve excluded any category_id values that are NULL by filtering them out in the WHERE clause. Because the category_id is not encapsulated in the COUNT function, it must be listed in the GROUP BY clause.
Example – Using GROUP BY with the MIN function
Let’s next look at how to use the GROUP BY clause with the MIN function in SQL.
In this example, we will use the employees table again that is populated the following data:
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:
Try It
SELECT dept_id, MIN(salary) AS lowest_salary FROM employees GROUP BY dept_id;
There will be 2 records selected. These are the results that you should see:
dept_id
lowest_salary
500
57500
501
42000
In this example, we’ve used the MIN function to return the lowest salary for each dept_id and we’ve aliased the results of the MIN function as lowest_salary. Because the dept_id is not encapsulated in the MIN function, it must be listed in the GROUP BY clause.
Example – Using GROUP BY with the MAX function
Finally, let’s look at how to use the GROUP BY clause with the MAX function.
Let’s use the employees table again, but this time find the highest salary for each dept_id:
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:
Try It
SELECT dept_id, MAX(salary) AS highest_salary FROM employees GROUP BY dept_id;
There will be 2 records selected. These are the results that you should see:
dept_id
highest_salary
500
62000
501
71000
In this example, we’ve used the MAX function to return the highest salary for each dept_id and we’ve aliased the results of the MAX function as highest_salary. The dept_id column must be listed in the GROUP BY clause because it is not encapsulated in the MAX function.
