SQL Group by and Having Interview Questions and Answers

GROUP BY and HAVING are where SQL interviews test whether you understand the order in which a query executes, not just its syntax. Almost every candidate can write a GROUP BY, but far fewer can explain why a non-aggregated column is rejected or why WHERE cannot see a COUNT. Those explanations are the whole point of this round, common in analyst, backend and data-engineering interviews.

The examples use orders(id, customer_id, region, status, amount, order_date) and standard SQL that runs on PostgreSQL, MySQL 8+, SQL Server and Oracle unless noted.

What does GROUP BY do?

GROUP BY partitions rows into groups sharing the same values in the listed columns, then collapses each group into a single output row on which aggregate functions operate.

The consequence that drives every rule below: after grouping, the query produces one row per group, so anything in SELECT must be either a grouping column or an aggregate. Lead with that and the tricky cases follow logically.

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

Q1. WHERE vs HAVING — explain the difference precisely.

WHERE filters rows before grouping and cannot use aggregates; HAVING filters groups after aggregation and can. Put row conditions in WHERE (earlier, cheaper) and aggregate conditions in HAVING.

SELECT region, SUM(amount) AS revenue
FROM orders
WHERE status = 'PAID'          -- row-level, before grouping
GROUP BY region
HAVING SUM(amount) > 100000;   -- group-level, after aggregation

The reason WHERE SUM(amount) > 100000 fails is that at the WHERE stage no groups exist yet, so no SUM exists to test. Stating that — rather than just "HAVING is for groups" — is the depth interviewers want.

Interview note: Trap: "is it ever fine to put a non-aggregate condition in HAVING?" It works but is wrong practice — it filters after grouping instead of before, wasting work. Keep row filters in WHERE.

Q2. Why must non-aggregated SELECT columns be in GROUP BY?

Because the group collapses to one row, and a column that is neither grouped nor aggregated has no single defined value for that row. Standard SQL rejects such queries.

-- Invalid in standard SQL: customer_id is neither grouped nor aggregated
-- SELECT region, customer_id, SUM(amount) FROM orders GROUP BY region;

-- Valid: aggregate the extra column or add it to GROUP BY
SELECT region, COUNT(DISTINCT customer_id) AS customers, SUM(amount) AS revenue
FROM orders GROUP BY region;

MySQL historically returned an arbitrary value for such columns (with ONLY_FULL_GROUP_BY disabled), which produced silent bugs. Mentioning that MySQL default now enforces the standard rule shows current, practical knowledge.

Interview note: Follow-up: "how does Postgres relax this?" If you GROUP BY a primary key, Postgres lets you SELECT other columns of that table functionally dependent on the key, since the value is then well-defined.

Q3. What is SQL's logical processing order and why does it matter?

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. This order explains what each clause can reference: WHERE runs before grouping (no aggregates), HAVING after (aggregates available), and SELECT aliases are created late.

SELECT region, SUM(amount) AS revenue   -- alias 'revenue' created here
FROM orders
WHERE amount > 0                        -- cannot use 'revenue' (not yet defined)
GROUP BY region
HAVING SUM(amount) > 1000               -- must repeat the aggregate, not the alias
ORDER BY revenue DESC;                  -- alias usable here (ORDER BY is last)

This single mental model resolves a whole family of "why can't I use this alias here" questions. ORDER BY can use the SELECT alias because it runs after SELECT; WHERE and usually HAVING cannot.

Interview note: Trap: "why does ORDER BY see the alias but WHERE doesn't?" Because ORDER BY is the last logical step, after SELECT defines the alias; WHERE is one of the first.

Q4. Can you GROUP BY an expression?

Yes — grouping on a computed expression is standard, and it is how you bucket by derived values like year, month, or a rounded number.

-- Revenue per year
SELECT EXTRACT(YEAR FROM order_date) AS yr, SUM(amount) AS revenue
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date)
ORDER BY yr;

Whether you may write GROUP BY yr (the alias) instead of repeating the expression is dialect-dependent: PostgreSQL and MySQL allow it as an extension, but standard SQL requires the full expression because SELECT is logically later than GROUP BY. Naming that portability caveat is a strong signal.

Interview note: Follow-up: "GROUP BY 1 — what does the number mean?" It groups by the first SELECT column by position. It works but is fragile; reordering SELECT silently changes the grouping.

Q5. How does GROUP BY treat NULLs?

All NULLs in the grouping column form a single group, even though NULL is not equal to NULL in normal comparisons. GROUP BY uses "not distinct" semantics, so NULLs collapse together.

-- Orders with no region collapse into one NULL group
SELECT region, COUNT(*) FROM orders GROUP BY region;
-- returns a row where region IS NULL with the count of region-less orders

This surprises candidates who expect each NULL to be separate. The takeaway: a nullable grouping column yields exactly one NULL bucket, which you can label with COALESCE(region, 'Unknown') for reporting.

Interview note: Trap: "how do you exclude the NULL group?" Add WHERE region IS NOT NULL before grouping, or HAVING region IS NOT NULL won't help because region isn't aggregated — filter in WHERE.

Q6. What does grouping by multiple columns produce?

One row per distinct combination of the listed columns. GROUP BY region, status produces a row for each region-and-status pair that exists in the data.

SELECT region, status, COUNT(*) AS cnt, SUM(amount) AS total
FROM orders
GROUP BY region, status
ORDER BY region, status;

The number of groups is at most the number of distinct combinations present — combinations with no rows simply don't appear (that's where CUBE/GROUPING SETS come in if you need the zeros). Being precise that only existing combinations produce rows is the correct nuance.

Interview note: Follow-up: "how do you also get per-region subtotals?" GROUP BY ROLLUP(region, status) adds region subtotals and a grand total in the same result.

Q7. Can HAVING be used without GROUP BY?

Yes — HAVING without GROUP BY treats the entire result as one group, so it filters based on an aggregate over all rows. The query returns either that one row or nothing.

-- Return the total only if there are more than 1000 orders
SELECT COUNT(*) AS total
FROM orders
HAVING COUNT(*) > 1000;

It is an uncommon but valid construct that interviewers use to test whether you really understand that HAVING filters groups — and that "no GROUP BY" means "one implicit group of everything". If the condition fails, you get zero rows, not a zero.

Interview note: Trap: "what does this return if the count is below the threshold?" No rows at all — the single group is filtered out. It does not return 0.

Q8. How do you filter groups on a condition combining WHERE and HAVING?

Use both: WHERE removes irrelevant rows first, then HAVING tests the aggregate of what remains. They compose, each doing the job it is designed for.

-- Regions whose PAID revenue exceeds 50k, considering only 2026 orders
SELECT region, SUM(amount) AS paid_revenue
FROM orders
WHERE status = 'PAID' AND order_date >= '2026-01-01'
GROUP BY region
HAVING SUM(amount) > 50000;

The interview-grade point is efficiency plus correctness: the WHERE conditions shrink the input before the expensive grouping, and only the genuinely aggregate condition lives in HAVING. Mixing them up — pushing the date filter into HAVING — is both slower and, for non-aggregate conditions, semantically wrong.

Interview note: Follow-up: "could the date filter go in HAVING?" Only via an aggregate like HAVING MIN(order_date) >= ..., which changes the meaning. Row filters belong in WHERE.

How to prepare

Write a query that groups on a nullable column and confirm the single NULL bucket appears, then deliberately try WHERE COUNT(*) > 5 and read the error — those two experiments cement the NULL-grouping rule and the logical-order rule better than any explanation. Practice narrating FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY out loud until it is automatic, because most GROUP BY follow-ups are really order-of-execution questions in disguise.

Pair this with the aggregate functions questions, since HAVING conditions are aggregate conditions, and the subqueries set, where grouped results are frequently wrapped and filtered further. The SQL learning path covers the logical query model that underlies every answer here. A mock interview centered on a reporting query is the best way to rehearse the WHERE-versus-HAVING judgment under pressure.