GROUP BY, HAVING and the Grain of a Result

Mark from Operations is back with the question you couldn’t answer last lesson: “What’s our average resolution time, by priority?” You know how to compute one ticket’s hours. Now you need one row per priority, each summarising dozens of tickets. That’s what GROUP BY does, and the idea underneath it, the grain of a result, is the single most useful concept in analytical SQL.

One row per what?

Every table and every result has a grain: the thing one row represents. support_tickets has one row per ticket. Mark wants one row per priority. Write that down first, then make the query match it:

SELECT
  priority,
  COUNT(*)                AS tickets,
  COUNT(closed_at)        AS closed,
  ROUND(AVG((julianday(closed_at) - julianday(opened_at)) * 24), 1) AS avg_hours,
  ROUND(MAX((julianday(closed_at) - julianday(opened_at)) * 24), 1) AS max_hours
FROM support_tickets
GROUP BY priority
ORDER BY avg_hours;

Four rows, one per priority, which matches the grain you stated. Urgent tickets close in 6.5 hours on average, high in about a day, and low and normal in over three days. Notice how COUNT(closed_at) and AVG quietly skip open tickets, exactly as the previous lessons promised. The max_hours column matters too: an average can look healthy while one customer waited a week.

That diagram explains most GROUP BY errors. WHERE runs before groups exist, so it can’t test COUNT(*); that’s HAVING’s job. ORDER BY runs after SELECT, so it can always use a column alias. SQLite and PostgreSQL also accept an alias in GROUP BY as a convenience, which is why GROUP BY month works below, but the logical order is still the one shown.

WHERE versus HAVING

Karim in Finance wants to know which products have sold at least 100 units at full price, with no discount. Units per product is an aggregate, so the threshold goes in HAVING. “No discount” is a property of each order line, so it’s a row condition and goes in WHERE:

SELECT
  product_id,
  SUM(quantity) AS units,
  COUNT(DISTINCT order_id) AS orders
FROM order_items
WHERE discount = 0
GROUP BY product_id
HAVING SUM(quantity) >= 100
ORDER BY units DESC
LIMIT 5;

Read it in the diagram’s order: drop discounted lines, group what’s left by product, keep products with 100+ units, then compute, sort and cut. COUNT(DISTINCT order_id) counts orders rather than lines, because in Cartwheel’s data each product appears at most once per order, so lines and orders differ only when you count across products.

Counting a subset inside each group

Huda in Marketing asks what share of each month’s orders came through the mobile app. You need two counts per month, all orders and app orders. A FILTER clause restricts one aggregate to some rows:

SELECT
  strftime('%Y-%m', order_date) AS month,
  COUNT(*) AS orders,
  COUNT(*) FILTER (WHERE channel = 'mobile_app') AS app_orders,
  ROUND(100.0 * COUNT(*) FILTER (WHERE channel = 'mobile_app') / COUNT(*), 1) AS app_pct
FROM orders
WHERE order_date >= '2025-07-01'
  AND order_date <  '2026-01-01'
GROUP BY month
ORDER BY month;

The app share moves between roughly 28% and 40%, and November’s volume is more than three times July’s, thanks to Black Friday. Two details matter. 100.0 rather than 100 forces decimal division; SQLite divides integers as integers, so 47 / 129 is 0. And FILTER is standard SQL, supported by SQLite and PostgreSQL. In engines without it, write SUM(CASE WHEN channel = 'mobile_app' THEN 1 ELSE 0 END), which is the same count spelled the long way.

Checking the grain

Once a grouped query runs, verify it before reporting:

1. Row count: does it match the number of groups you expect (4 priorities, 6 months)?
2. Uniqueness: is the GROUP BY key unique in the output? It is by construction, unless a bare column is hiding something.
3. Totals: does the sum of a column across groups equal the ungrouped total? For tickets, the four counts add up to 873.

None of these checks takes more than a minute, and together they catch the majority of grouping mistakes before a stakeholder does. Make them part of the loop from the first lesson, between “query” and “answer”. That third check becomes vital in the next lessons, when you start joining tables. Joins can change the grain without telling you, and summing over the wrong grain is how double counting starts.