SQL GROUP BY and HAVING

In SQL Aggregate Functions you turned a whole table into one number, such as the total salary bill. Often you need that number for each something: staff per department, customers per state, orders per customer. SQL GROUP BY does this by splitting rows into groups and running the aggregate once per group. HAVING then lets you keep only the groups you care about.

How SQL GROUP BY works

Picture the database sorting rows into buckets. Every row with the same value in the GROUP BY column lands in the same bucket. Each bucket then becomes one row in the result, and aggregate functions such as COUNT or AVG are calculated inside each bucket.

SELECT Department,
       COUNT(*)            AS Staff,
       ROUND(AVG(Salary))  AS AvgSalary
FROM Employees
GROUP BY Department;

Eight employee rows became five result rows – one per department. Without GROUP BY, the same COUNT(*) would have returned a single row saying 8.

The golden rule

Every column in the SELECT list must either be in the GROUP BY clause or be inside an aggregate function. In the query above, Department is grouped, and Salary only appears inside AVG(). If you added FirstName, which name should the database show for a group of three developers? There is no single answer.

Grouping with WHERE and ORDER BY

GROUP BY sits after WHERE and before ORDER BY. WHERE removes rows before they are grouped, and ORDER BY can sort by the aggregate result. Here we count Indian customers per state and show the busiest states first:

SELECT State, COUNT(*) AS Customers
FROM Customers
WHERE Country = 'India'
GROUP BY State
ORDER BY Customers DESC, State;

You can group by more than one column, such as GROUP BY Country, State. Each different combination then forms its own group. You can also group by an expression. Because our dates are text, SQLite’s strftime('%Y-%m', OrderDate) extracts the month, so GROUP BY strftime('%Y-%m', OrderDate) gives orders per month. In MySQL you would use DATE_FORMAT(OrderDate, '%Y-%m').

Filtering groups with HAVING

Suppose you want only customers who have placed more than one order. You cannot write WHERE COUNT(*) > 1, because WHERE runs before the groups exist – at that point there is nothing to count. HAVING is the filter that runs after grouping, so it can use aggregate results.

SELECT CustomerID, COUNT(*) AS OrderCount
FROM Orders
GROUP BY CustomerID
HAVING COUNT(*) > 1;

Showing CustomerID numbers is not very friendly. In SQL Joins you will combine this with the Customers table to show names instead.

WHERE and HAVING together

The two filters often work side by side. The next query looks only at products under ₹300 (WHERE), groups them by category, and keeps categories that still have at least three such products (HAVING).

SELECT CategoryID,
       COUNT(*)   AS Products,
       MAX(Price) AS Dearest
FROM Products
WHERE Price < 300
GROUP BY CategoryID
HAVING COUNT(*) >= 3
ORDER BY CategoryID;

The order SQL actually runs a query

You write SELECT first, but the database processes the clauses in a different order. Knowing this order explains most GROUP BY errors:

1. FROM – pick the table (and joins)
2. WHERE – remove rows
3. GROUP BY – form groups
4. HAVING – remove groups
5. SELECT – calculate the output columns
6. ORDER BY – sort
7. LIMIT – cut down the rows

This is why a SELECT alias cannot normally be used in WHERE: when WHERE runs, the alias does not exist yet. ORDER BY comes after SELECT, so aliases work there. Next, learn to combine tables in SQL Joins.

Further reading

• sqlite.org/lang_select.html#resultset
• sqlite.org/lang_aggfunc.html
• dev.mysql.com/doc/refman/8.4/en/group-by-handling.html
• dev.mysql.com/doc/refman/8.4/en/select.html