Two-column GROUP BY, and HAVING

New file for this lesson. Ninety support tickets from three months of a fictional help desk, with a channel, a priority, a category, two timing columns and a satisfaction score that is sometimes missing. It is a better shape than the sales file for what this lesson is about, because the interesting questions are all about crossing one category with another.

Note the empty cells in the last column. Twenty of the ninety tickets have no satisfaction score, because nobody rates a ticket that is still open. Those empty cells become NULL when the file is read, and they are going to matter before the end of this lesson.

Grouping by a pair

Add a second column to the GROUP BY and the buckets get finer. Three channels crossed with four priorities gives at most twelve buckets, and the query returns exactly the combinations that actually occur in the file rather than all twelve regardless. If nobody ever raised an urgent ticket by chat, that row simply is not in the output.

That absence is worth pausing on, because it is the difference between a SQL result and a spreadsheet pivot. A pivot draws the full grid and leaves blanks. SQL returns rows that exist. When a dashboard needs the empty combinations shown as zero, you have to manufacture them, and the usual tool is a cross join against the list of categories.

The columns in a GROUP BY have no privileged order. Grouping by channel and priority produces the same set of rows as grouping by priority and channel; only the column order in the output changes, and only if you also swap them in the select list. Order the select list the way the reader will scan it.

HAVING filters groups

You cannot put an aggregate in a WHERE clause. WHERE COUNT(*) >= 10 is an error, and the reason is timing rather than fussiness: WHERE runs while the engine is still looking at individual rows, before any bucket exists, so there is nothing to count yet.

HAVING is the same idea applied after the grouping. It sits directly after GROUP BY and its condition is evaluated once per bucket:

SELECT channel, priority, COUNT(*) AS tickets
FROM csv
GROUP BY channel, priority
HAVING COUNT(*) >= 10
ORDER BY tickets DESC

Three combinations clear that bar on this file. HAVING is what you reach for whenever the question contains the words "at least", "more than" or "only the ones with", applied to a group rather than a row.

WHERE and HAVING in one query

They are not alternatives, and a query can sensibly use both:

SELECT category, COUNT(*) AS tickets
FROM csv
WHERE status = 'Resolved'
GROUP BY category
HAVING COUNT(*) > 8

Read it top to bottom and it is the order of operations: keep resolved tickets, bucket them by category, keep buckets with more than eight. Putting a row-level condition in HAVING would usually still give the right answer, but it makes the engine build buckets it is about to throw away, and on a large table that is real time. Filter early.

Nulls, one more time

The satisfaction column is where lesson 6 pays off. COUNT(*) counts tickets; COUNT(satisfaction) counts the ones that were rated. Put both in one grouped query and the two columns side by side tell you your response rate per status without a single explicit null check. On this file every Resolved and Closed ticket is rated and no Open or Pending one is, which is exactly the pattern you would hope to see and exactly the sort of thing worth verifying rather than assuming.

AVG(satisfaction) over the same groups divides by the rated tickets only, which is almost certainly what you want. If somebody asks why the average satisfaction is not the total divided by the ticket count, that is the answer.

Reading a grouped result critically

One of the exercises below asks for average first response time by priority, and the answer is a clean ladder: urgent tickets are answered in minutes, low priority ones in hours. That is what a healthy support process looks like in data, and it is worth noticing that you can see the process working from four numbers.

It is also worth noticing what those four numbers hide. An average of eleven minutes could be every ticket at eleven minutes, or half at one minute and half at twenty-one. Aggregates are lossy by design, and the discipline is to remember what you gave up when you collapsed a hundred rows into four. A median and a maximum alongside the average cost nothing extra and tell you a much fuller story.

What to look up next

Look up FILTER (WHERE ...), the standard SQL clause that DuckDB and Postgres both support. It lets you write COUNT(*) FILTER (WHERE priority = 'Urgent') beside a plain count in the same grouped query, which is far clearer than the CASE trick most people learn first.