How does GROUP BY work with aggregate functions? | Interview Question

Q: How does GROUP BY work with aggregate functions?

**Target Audience:** Beginners writing their first "total per category" query, and candidates who need the single-value rule and the engine differences. **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16. MySQL behaviour is cited from its manual. > **How to read this doc:** Each idea is explained in plain language first, then tagged with `📌 Interview term:` so you learn the words an interviewer expects. Every result, plan and error shown was produced by actually running the query; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First After a garage sale, you tip the cash box onto the table: nine receipts. Someone asks "how much did we make on each kind of item?". You make three piles, laptops, monitors and mice, and then write one line per pile: how many receipts, and their total. That is GROUP BY. Making the piles is grouping; writing one summary l

The problem

Question presented to candidate: "Given a sales table and a reps table, show revenue per product and per region per month. Then explain what GROUP BY actually does to the rows, and why some databases reject SELECT product, rep_id, MAX(amount) ... GROUP BY product while others run it."

What a strong answer should cover:

GROUP BY splits the filtered rows into groups with equal grouping keys and returns one row per group; aggregates are computed per group
The single-value rule: every SELECT, HAVING and ORDER BY expression must be a grouping key, an aggregate, or functionally dependent on the keys (PostgreSQL allows other columns of a table grouped by its primary key)
Engine differences: PostgreSQL and MySQL with ONLY_FULL_GROUP_BY reject bare columns; SQLite accepts them and, with a single MIN or MAX, takes them from that row; ANY_VALUE is the explicit form
NULL keys form one group; grouping by an expression; no GROUP BY means one group (one row even on empty input), GROUP BY on empty input returns no rows
Where it sits in the logical order (after WHERE, before HAVING), how engines execute it (hash or sort), and when to use a window function instead

Code / implementation expected: Yes — grouping by one column, by several columns and an expression, and the bare-column case.

Schema used by the examples:

group-by
aggregate

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners writing their first "total per category" query, and candidates who need the single-value rule and the engine differences. Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16. MySQL behaviour is cited from its manual.

How to read this doc: Each idea is explained in plain language first, then tagged with 📌 Interview term: so you learn the words an interviewer expects. Every result, plan and error shown was produced by actually running the query; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

After a garage sale, you tip the cash box onto the table: nine receipts. Someone asks "how much did we make on each kind of item?". You make three piles, laptops, monitors and mice, and then write one line per pile: how ma

The solution is hidden so you can try first

13 min read · 10 sections

Run the code

Edit the query and run it against the tables for this question.

SQLOne grouping key: one output row per product

SQLSeveral keys and an expression: revenue per region per month, with a NULL region group

SQLSQLite only: a bare column next to MAX() comes from the max row (PostgreSQL rejects this)

PostgreSQL: GROUP BY the primary key lets you select the other columns of that table

SELECT r.id, r.name, r.region,
       COUNT(*) AS sales,
       SUM(s.amount) AS revenue,
       ANY_VALUE(s.product) IS NOT NULL AS has_product
FROM reps AS r
JOIN sales AS s ON s.rep_id = r.id
GROUP BY r.id
ORDER BY r.id;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.