GROUP BY and HAVING

Why it matters

One total for the whole table is useful, but most questions are "per something": revenue per month, tickets per team, average score per class. GROUP BY splits the rows into groups and runs the aggregates once per group. HAVING then filters the groups. Together they turn a table into a report.

GROUP BY: one row per group

Example support_tickets table:

SELECT team,
       COUNT(*) AS tickets,
       AVG(hours_to_resolve) AS avg_hours
FROM support_tickets
GROUP BY team;

The database gathers rows with the same team into a bucket, then computes COUNT and AVG for each bucket. Each bucket becomes one output row.

The golden rule

Every column in SELECT must either be in the GROUP BY or be inside an aggregate. team is in the GROUP BY; COUNT(*) and AVG(...) are aggregates. If you added priority to the SELECT without grouping by it, which priority should the Billing row show? It has both "high" and "low". Strict engines refuse; SQLite picks one arbitrarily, which is worse because it looks like it worked.

Grouping by several columns

Group by more than one column to get a row per combination:

SELECT team, priority, COUNT(*) AS tickets
FROM support_tickets
GROUP BY team, priority;

Billing now produces two rows, (Billing, high, 2) and (Billing, low, 1).

You can also group by an expression, such as a month computed from a date: GROUP BY strftime('%Y-%m', created_at).

WHERE vs HAVING

Both filter, but at different times:

1. FROM picks the table.
2. WHERE removes rows, before grouping.
3. GROUP BY forms groups.
4. HAVING removes groups, after aggregating.
5. SELECT produces the columns, then ORDER BY sorts.

So WHERE cannot see aggregates (they do not exist yet), and HAVING is the place for conditions like "teams with at least 3 tickets":

SELECT team, COUNT(*) AS tickets
FROM support_tickets
GROUP BY team
HAVING COUNT(*) >= 3;

Only Billing remains.

Use both in one query when you need to:

-- Among high-priority tickets only, teams averaging over 3 hours
SELECT team, AVG(hours_to_resolve) AS avg_hours
FROM support_tickets
WHERE priority = 'high'
GROUP BY team
HAVING AVG(hours_to_resolve) > 3;

WHERE throws away low-priority rows first; HAVING then keeps teams whose high-priority average is over 3. Billing (4 and 6, average 5) stays; Network (2) goes.

A good habit: if a condition does not involve an aggregate, put it in WHERE. It is clearer and lets the database discard rows early.

Sorting grouped results

Groups come back in no guaranteed order. Add ORDER BY, often on an aggregate:

SELECT team, COUNT(*) AS tickets
FROM support_tickets
GROUP BY team
ORDER BY tickets DESC;

Most engines let you sort by the alias (tickets), because ORDER BY runs after SELECT. Some engines, including SQLite and MySQL, also accept the alias in HAVING, but PostgreSQL and SQL Server do not, so repeating the aggregate in HAVING is the portable choice.

Worked example

A help-desk lead asks: "For each team with at least two tickets, how many tickets and what is the average resolution time, rounded to one decimal, slowest team first?"

SELECT team,
       COUNT(*) AS tickets,
       ROUND(AVG(hours_to_resolve), 1) AS avg_hours
FROM support_tickets
GROUP BY team
HAVING COUNT(*) >= 2
ORDER BY avg_hours DESC;

Reading it in processing order makes it easy to check: all tickets, grouped by team, groups with two or more kept, columns computed, sorted.

Common mistakes

• Selecting a column that is neither grouped nor aggregated.
• Putting aggregate conditions in WHERE. WHERE COUNT(*) > 2 is an error; use HAVING.
• Putting row conditions in HAVING. It may work, but it filters late and confuses readers.
• Forgetting that NULL forms its own group. Rows with a missing team are grouped together under NULL.
• Expecting sorted groups without ORDER BY.

Quick recap

• GROUP BY produces one output row per distinct value (or combination).
• Every selected column is grouped or aggregated.
• WHERE filters rows before grouping; HAVING filters groups after.
• Add ORDER BY to control the order of groups.

Try it

Open the Do tab and build a per-team help-desk report that only includes busy teams.