GROUP BY and HAVING for Reporting

A top-line total answers "how much did we sell?" but the interesting business questions are always segmented: how much per region, per month, per product, per customer segment. GROUP BY is the clause that produces those breakdowns, and HAVING is how you keep only the segments worth reporting. Together they turn a flat table of orders into the kind of dimensional report that fills a dashboard.

This builds directly on aggregate functions — make sure SUM, COUNT and AVG feel natural before continuing. It is a central stop on the SQL for analytics path.

From one total to a breakdown

Here is the sample orders table:

-- orders
order_id | customer | region | product  | amount | order_date
---------+----------+--------+----------+--------+-----------
1001     | Aarti    | South  | Keyboard | 1200   | 2026-01-05
1002     | Bhaskar  | South  | Monitor  | 8500   | 2026-01-11
1003     | Chitra   | West   | Keyboard | 1200   | 2026-02-02
1004     | Aarti    | South  | Mouse    | 600    | 2026-02-18
1005     | Devan    | North  | Monitor  | 8500   | 2026-03-09
1006     | Chitra   | West   | Headset  | 2100   | 2026-03-22

SUM(amount) alone gives one number for the whole table. Add GROUP BY region and the database splits the rows into per-region groups, then sums each group:

SELECT region,
       COUNT(*)    AS orders,
       SUM(amount) AS revenue
FROM orders
GROUP BY region;

region | orders | revenue
-------+--------+--------
South  | 3      | 10300
West   | 2      | 3300
North  | 1      | 8500

One row per region, each with its own order count and revenue. Swap region for product and you get a per-product breakdown from the same pattern. This is the fundamental move of segmented reporting.

The rule that causes most GROUP BY errors

Memorize this: every column in the SELECT list must either appear in GROUP BY or be inside an aggregate function. Break it and the query fails.

-- ERROR: customer is neither grouped nor aggregated
SELECT region, customer, SUM(amount)
FROM orders
GROUP BY region;

Once rows collapse into the South group there are three different customers in it, so the database cannot pick one value for customer. Either add it to the grouping key or aggregate it away. The mental model that fixes this permanently: after GROUP BY, each group is one output row, so any column you display must have exactly one value per group.

Grouping by multiple dimensions

GROUP BY region, product makes one group per unique combination, producing a cross-tab:

SELECT region, product,
       SUM(amount) AS revenue
FROM orders
GROUP BY region, product
ORDER BY region, revenue DESC;

This is how you build reports like "revenue by region and product category" — two dimensions at once. The more columns you group by, the finer and more numerous the groups.

Grouping by month

A constant analyst need is time-series summaries. Group by a truncated date so every order in a month lands in one group:

SELECT DATE_TRUNC('month', order_date) AS month,
       SUM(amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

month      | revenue
-----------+--------
2026-01-01 | 9700
2026-02-01 | 1800
2026-03-01 | 10600

The exact date function varies by database — DATE_TRUNC in PostgreSQL, DATE_FORMAT in MySQL, FORMAT in SQL Server — but the idea is identical: collapse the timestamp to the granularity you want, then aggregate.

Filtering groups with HAVING

WHERE filters rows before grouping. But "only regions with revenue above 5000" is a condition on a group total that does not exist until after grouping. That is what HAVING is for:

SELECT region,
       SUM(amount) AS revenue
FROM orders
GROUP BY region
HAVING SUM(amount) > 5000;

region | revenue
-------+--------
South  | 10300
North  | 8500

West drops out because its 3300 is below the threshold. You often use both clauses together — WHERE to discard irrelevant rows first, then HAVING to keep only qualifying groups:

SELECT region,
       SUM(amount) AS revenue
FROM orders
WHERE order_date >= '2026-01-01'   -- filter rows
GROUP BY region
HAVING SUM(amount) > 5000          -- filter groups
ORDER BY revenue DESC;

Common mistakes

• Using WHERE to filter an aggregate. WHERE SUM(amount) > 5000 fails; the count or sum does not exist yet. Use HAVING.
• Selecting an ungrouped, unaggregated column. The single most common GROUP BY error. Every displayed column must be a grouping key or an aggregate.
• Grouping by a raw timestamp for a monthly report. If each order has a distinct time, grouping by the raw column gives one group per order. Truncate the date to month first.
• Double-counting after a join. Joining before grouping can multiply rows and inflate SUM. Verify totals against a known figure when a join precedes the grouping.

In interviews

GROUP BY questions are the core of analyst SQL screens: "revenue per region", "orders per customer", "monthly sales trend", "regions above a revenue target". The near-guaranteed conceptual question is "what is the difference between WHERE and HAVING?" — answer that WHERE filters rows before aggregation and cannot use aggregates, HAVING filters groups after and can, and mention the execution order to show depth. Interviewers also probe the ungrouped-column rule, so be ready to explain why it exists.

Where this fits in your learning path

GROUP BY and HAVING turn the aggregate functions into segmented reports, the backbone of dashboards. Once you want names instead of IDs in those reports, you combine grouping with joins. And when you need per-row detail alongside a group summary rather than collapsing it, move to window functions. All three recur throughout the data analyst roadmap.