SQL GROUP BY clause

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

grouped rows

GROUP BY clause withSELECTstatement andAggregate functionsused together to group rows by common column values

To make it easier to understand this, let's take a look at the followingemployeesAnddepartmentsTable.

+--------+--------------+------------+---------+
| 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, it is not just to find the employees and the names of their departments, but to find the total number of employees in each department.

For small tables, you can simply applyLeft joinand calculate the number of employees, but suppose if a table contains thousands of employees, it will not be so easy.

In this case, you can use the GROUP BY clause together with the SELECT statement as follows:

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 will get the following output:

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

In the next chapter, you will learn how to use the HAVING clause and the GROUP BY clause to specify search conditions for groups or aggregates.