The GROUP BY clause is used with the SELECT statement and aggregate functions to organize rows based on common column values.
To grasp this concept clearly, let's examine the 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 | +---------+------------------+ |
|
Table: employees |
Table: departments |
Now, let's say you want to determine the total number of employees in each department instead of just their names.
If dealing with smaller tables, a simple left join followed by counting the employees is straightforward. However, handling tables with thousands of employees becomes more challenging.
In such cases, you can utilize the GROUP BY clause with the SELECT statement, demonstrated 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 run the above query, you'll receive output similar to this:
+-------------------+-----------------+ | dept_name | total_employees | +-------------------+-----------------+ | Administration | 1 | | Customer Service | 0 | | Finance | 1 | | Human Resources | 1 | | Sales | 1 | +-------------------+-----------------+
In the following chapter, you'll discover how to define a search condition for a group or an aggregate using the HAVING clause alongside the GROUP BY clause.