Curriculum

SQL GROUP BY

In this tutorial, you will learn how to use the SQL GROUP BY clause to group rows based on one or more columns.

Introduction to SQL GROUP BY clause

The GROUP BY is an optional clause of the SELECT statement. The GROUP BY clause allows you to group rows based on values of one or more columns. It returns one row for each group.

The following shows the basic syntax of the GROUP BY clause:

SELECT
    column1,
    column2,
    aggregate_function(column3)
FROM
    table_name
GROUP BY
    column1,
    column2;

The table on the left side has two columns id and fruit. When you apply the GROUP BY clause to the fruit column, it returns the result set that includes unique values from the fruit column:

SELECT 
    fruit
FROM 
    sample_table
GROUP BY 
    fruit;

In practice, you often use the GROUP BY clause with an aggregate function such as MIN, MAX, AVG, SUM, or COUNT to calculate a measure that provides the information for each group.

In this example, we group the rows by the values of the fruit column and apply the COUNT function to the id column. The result set includes the unique values of the fruit columns and the number of the corresponding rows.

SELECT 
   fruit, COUNT(id) 
FROM
   sample_table
GROUP BY 
   fruit;

The columns that appear in the GROUP BY clause are called grouping columns. If a grouping column contains NULL values, all NULL values are summarized into a single group because the GROUP BY clause considers all NULL values equal.

SQL GROUP BY examples

We will use the employees and departments tables in the sample database to demonstrate how the GROUP BY clause works.

The following example uses the GROUP BY clause to group the values in department_id column of the employees table:

SELECT 
    department_id
FROM 
    employees
GROUP BY 
    department_id;

+---------------+
| department_id |
+---------------+
|             1 |
|             2 |
|             3 |
|             4 |
|             5 |
|             6 |
|             7 |
|             8 |
|             9 |
|            10 |
|            11 |
+---------------+
11 rows in set (0.00 sec)

In this example:

First, the SELECT clause returns all values from the department_id column of employees table.
Second, the GROUP BY clause groups all values into groups.

The department_id column of the employees table has 40 rows, including duplicate department_id values. However, the GROUP BY groups these values into groups.

Without an aggregate function, the GROUP BY behaves like the DISTINCT keyword:

SELECT 
   DISTINCT department_id
FROM 
   employees
ORDER BY 
   department_id;

The GROUP BY clause will be more useful when you use it with an aggregate function.

For example, the following statement uses the GROUP BY clause with the COUNT function to count the number of employees by department:

SELECT
    department_id,
    COUNT(employee_id) headcount
FROM
    employees
GROUP BY
    department_id;

+---------------+-----------+
| department_id | headcount |
+---------------+-----------+
|             1 |         1 |
|             2 |         2 |
|             3 |         6 |
|             4 |         1 |
|             5 |         7 |
|             6 |         5 |
|             7 |         1 |
|             8 |         6 |
|             9 |         3 |
|            10 |         6 |
|            11 |         2 |
+---------------+-----------+
11 rows in set (0.00 sec)

How it works.

First, the GROUP BY clause groups the rows in the employees table by department id.
Second, the COUNT(employee_id) returns the number of employee id values in each group.

SQL GROUP BY with INNER JOIN example

The following example returns the number of employees by department. And it uses an INNER JOIN clause to include the department name in the result:

SELECT
    department_name,
    COUNT(employee_id) headcount
FROM
    employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
        department_name;

+------------------+-----------+
| department_name  | headcount |
+------------------+-----------+
| Accounting       |         2 |
| Administration   |         1 |
| Executive        |         3 |
| Finance          |         6 |
| Human Resources  |         1 |
| IT               |         5 |
| Marketing        |         2 |
| Public Relations |         1 |
| Purchasing       |         6 |
| Sales            |         6 |
| Shipping         |         7 |
+------------------+-----------+
11 rows in set (0.01 sec)

SQL GROUP BY with ORDER BY example

The following example uses an ORDER BY clause to sort the departments by headcount:

SELECT 
    department_name,
    COUNT(employee_id) headcount
FROM
    employees e
        INNER JOIN
    departments d ON d.department_id = e.department_id
GROUP BY department_name
ORDER BY headcount DESC;

+------------------+-----------+
| department_name  | headcount |
+------------------+-----------+
| Shipping         |         7 |
| Sales            |         6 |
| Finance          |         6 |
| Purchasing       |         6 |
| IT               |         5 |
| Executive        |         3 |
| Marketing        |         2 |
| Accounting       |         2 |
| Human Resources  |         1 |
| Administration   |         1 |
| Public Relations |         1 |
+------------------+-----------+
11 rows in set (0.00 sec)

Note that you can use either the headcount alias or the COUNT(employee_id) in the ORDER BY clause.

SQL GROUP BY with HAVING example

The following example uses the HAVING clause to find departments with headcounts are greater than 5:

SELECT 
    department_name,
    COUNT(employee_id) headcount
FROM
    employees e
        INNER JOIN
    departments d ON d.department_id = e.department_id
GROUP BY department_name
HAVING headcount > 5
ORDER BY headcount DESC;

+-----------------+-----------+
| department_name | headcount |
+-----------------+-----------+
| Shipping        |         7 |
| Sales           |         6 |
| Finance         |         6 |
| Purchasing      |         6 |
+-----------------+-----------+
4 rows in set (0.00 sec)

SQL GROUP BY with MIN, MAX, and AVG example

The following query returns the minimum, maximum, and average salary of employees in each department.

SELECT 
    department_name,
    MIN(salary) min_salary,
    MAX(salary) max_salary,
    ROUND(AVG(salary), 2) average_salary
FROM
    employees e
        INNER JOIN
    departments d ON d.department_id = e.department_id
GROUP BY 
    department_name;

+------------------+------------+------------+----------------+
| department_name  | min_salary | max_salary | average_salary |
+------------------+------------+------------+----------------+
| Accounting       |    8300.00 |   12000.00 |       10150.00 |
| Administration   |    4400.00 |    4400.00 |        4400.00 |
| Executive        |   17000.00 |   24000.00 |       19333.33 |
| Finance          |    6900.00 |   12000.00 |        8600.00 |
| Human Resources  |    6500.00 |    6500.00 |        6500.00 |
| IT               |    4200.00 |    9000.00 |        5760.00 |
| Marketing        |    6000.00 |   13000.00 |        9500.00 |
| Public Relations |   10000.00 |   10000.00 |       10000.00 |
| Purchasing       |    2500.00 |   11000.00 |        4150.00 |
| Sales            |    6200.00 |   14000.00 |        9616.67 |
| Shipping         |    2700.00 |    8200.00 |        5885.71 |
+------------------+------------+------------+----------------+
11 rows in set (0.01 sec)

SQL GROUP BY with SUM function example

To get the total salary per department, you apply the SUM function to the salary column and group employees by the department_id column as follows:

SELECT 
    department_name,
    SUM(salary) total_salary
FROM
    employees e
        INNER JOIN
    departments d ON d.department_id = e.department_id
GROUP BY 
    department_name;

+------------------+--------------+
| department_name  | total_salary |
+------------------+--------------+
| Accounting       |     20300.00 |
| Administration   |      4400.00 |
| Executive        |     58000.00 |
| Finance          |     51600.00 |
| Human Resources  |      6500.00 |
| IT               |     28800.00 |
| Marketing        |     19000.00 |
| Public Relations |     10000.00 |
| Purchasing       |     24900.00 |
| Sales            |     57700.00 |
| Shipping         |     41200.00 |
+------------------+--------------+
11 rows in set (0.01 sec)

SQL GROUP BY multiple columns

So far, you have seen that we have grouped all employees by one column. For example, the following clause places all rows with the same values in the department_id column in one group.

GROUP BY department_id

How about grouping employees by values in both department_id and job_id columns?

GROUP BY department_id, job_id

This clause will group all employees with the same values in both department_id and job_id columns in one group.

The following statement groups rows with the same values in both department_id and job_id columns in the same group then return the rows for each of these groups.

SELECT 
    department_name,
    job_title,
    COUNT(employee_id)
FROM
    employees e
        INNER JOIN
    departments d ON d.department_id = e.department_id
        INNER JOIN
    jobs j ON j.job_id = e.job_id
GROUP BY department_name , 
         job_title;

+------------------+---------------------------------+--------------------+
| department_name  | job_title                       | COUNT(employee_id) |
+------------------+---------------------------------+--------------------+
| Accounting       | Accounting Manager              |                  1 |
| Accounting       | Public Accountant               |                  1 |
| Administration   | Administration Assistant        |                  1 |
| Executive        | Administration Vice President   |                  2 |
| Executive        | President                       |                  1 |
| Finance          | Accountant                      |                  5 |
| Finance          | Finance Manager                 |                  1 |
| Human Resources  | Human Resources Representative  |                  1 |
| IT               | Programmer                      |                  5 |
| Marketing        | Marketing Manager               |                  1 |
| Marketing        | Marketing Representative        |                  1 |
| Public Relations | Public Relations Representative |                  1 |
| Purchasing       | Purchasing Clerk                |                  5 |
| Purchasing       | Purchasing Manager              |                  1 |
| Sales            | Sales Manager                   |                  2 |
| Sales            | Sales Representative            |                  4 |
| Shipping         | Shipping Clerk                  |                  2 |
| Shipping         | Stock Clerk                     |                  1 |
| Shipping         | Stock Manager                   |                  4 |
+------------------+---------------------------------+--------------------+
19 rows in set (0.00 sec)

SQL