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:

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:

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:

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:

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.