SQL GROUP BY Clause

In this tutorial you will learn how to group rows based on column values.

Grouping Rows

The GROUP BY clause is used in conjunction with the SELECT statement and aggregate functions to group rows together by common column values

To understand this easily, let's look at the following employees and departments tables.

+--------+--------------+------------+---------+
| emp_id | emp_name     | hire_date  | dept_id |
+--------+--------------+------------+---------+
|      1 | Ethan Hunt   | 2001-05-01 |       4 |
|      2 | Tony Montana | 2002-07-15 |       1 |
|      3 | Sarah Connor | 2005-10-18 |       5 |
|      4 | Rick Deckard | 2007-01-03 |       3 |
|      5 | Martin Blank | 2008-06-24 |    NULL |
+--------+--------------+------------+---------+

+---------+------------------+
| dept_id | dept_name        |
+---------+------------------+
|       1 | Administration   |
|       2 | Customer Service |
|       3 | Finance          |
|       4 | Human Resources  |
|       5 | Sales            |
+---------+------------------+

Now, let's say instead of finding just name of the employees and their departments, you want to find out the total number of employees in every department.

In case of small tables you can simply apply the left join and count the number of employees, but suppose if a table contains thousands of employees then it wouldn't be so easy.

In this situation you can use the GROUP BY clause with the SELECT statement, like this:

SELECT t1.dept_name, count(t2.emp_id) AS total_employees
FROM departments AS t1 LEFT JOIN employees AS t2
ON t1.dept_id = t2.dept_id
GROUP BY t1.dept_name;

If you execute the above statement, you'll get the output something like this:

+-------------------+-----------------+
| dept_name         | total_employees |
+-------------------+-----------------+
| Administration    |               1 |
| Customer Service  |               0 |
| Finance           |               1 |
| Human Resources   |               1 |
| Sales             |               1 |
+-------------------+-----------------+

In the next chapter you'll learn how to specify a search condition for a group or an aggregate using the HAVING clause with the GROUP BY clause.