Grouping Records — GROUP BY and HAVING

An aggregate function on its own gives one number for the whole table. The GROUP BY clause lets us instead split the rows into groups that share the same value in one or more columns, and then apply the aggregate function to each group separately. This answers questions of the form 'for each department, ...'.

For example, to find the number of employees in each department, we group by Dept and count within each group:

SELECT Dept, COUNT(*) AS NumEmployees
FROM Employee
GROUP BY Dept;

The result has one row per department:

Similarly, the total and average salary of each department:

SELECT Dept, SUM(Salary) AS TotalSalary, AVG(Salary) AS AvgSalary
FROM Employee
GROUP BY Dept;

The golden rule of GROUP BY: every column named in the SELECT list must be either (a) one of the columns in the GROUP BY, or (b) inside an aggregate function. It makes no sense, for instance, to select an individual Name alongside a per-department count, because a department has many names but only one count.

Filtering groups — HAVING. A WHERE clause filters individual rows before they are grouped; it cannot test an aggregate. To filter the groups themselves — after the aggregate has been worked out — we use a separate clause, HAVING. For example, to list only those departments whose average salary is above 29000:

SELECT Dept, AVG(Salary) AS AvgSalary
FROM Employee
GROUP BY Dept
HAVING AVG(Salary) > 29000;
``` …