🎓 Velnov

🗄️ SQL · Topic 7 of 20

GROUP BY & HAVING — SQL Questions with Answers

30 practice questions on GROUP BY & HAVING, each with the correct answer and a clear explanation of why it is right. Read through them as revision notes, or take this topic as an interactive quiz to test yourself.

1 easy · 16 medium · 13 hard · Part of SQL: Databases from basics to window functions - 20 topics.

Key concepts in this topic

GROUP BYAggregatesRulesONLY_FULL_GROUP_BYHAVINGWHEREExecution orderPerformanceMultiple columnsORDER BYNULLDuplicatesBusiness queriesAliasesDialectsDatesDATE_TRUNCPositional groupingWHERE vs HAVINGROLLUPCUBEReportingAVGCOUNT DISTINCT

Jump to: Easy · Medium · Hard

Easy questions (1)

Q1. easy

What does GROUP BY do?

SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city;
  1. A Collapses rows sharing the same value into ONE row per group, so aggregates can be computed per group
  2. B Sorts the rows
  3. C Filters the rows
  4. D Joins tables

Answer: A — Collapses rows sharing the same value into ONE row per group, so aggregates can be computed per group

GROUP BY city turns a table of customers into one row per city, letting COUNT(*) report customers per city.

GROUP BYAggregates

Medium questions (16)

Q2. medium

Which columns may appear in SELECT alongside GROUP BY?

  1. A Only the grouped columns and aggregate functions
  2. B Any column
  3. C Only aggregates
  4. D Only the first column

Answer: A — Only the grouped columns and aggregate functions

Every other column would have many possible values per group, so the database cannot pick one. Group by it, or aggregate it with MAX/MIN.

GROUP BYRules

Q4. medium

What does HAVING do?

SELECT city, COUNT(*) AS n
FROM customers
WHERE active = 1      -- rows
GROUP BY city
HAVING COUNT(*) > 10; -- groups
  1. A Filters the GROUPS after aggregation - it can use aggregate functions
  2. B Filters rows before grouping
  3. C Sorts groups
  4. D Is the same as WHERE

Answer: A — Filters the GROUPS after aggregation - it can use aggregate functions

WHERE filters raw rows first; HAVING then filters the summarised groups. Both can appear in one query.

HAVINGWHERE

Q6. medium

Which is more efficient for filtering rows that will never be needed?

  1. A WHERE - it removes rows BEFORE the expensive grouping
  2. B HAVING - it filters at the end
  3. C They are identical
  4. D Neither

Answer: A — WHERE - it removes rows BEFORE the expensive grouping

Filter as early as possible. Reserve HAVING for conditions that genuinely depend on aggregated values.

PerformanceWHERE

Q7. medium

Can you GROUP BY several columns?

  1. A Yes - it groups by each unique COMBINATION of those columns
  2. B No, only one column
  3. C Only with a JOIN
  4. D Only two columns maximum

Answer: A — Yes - it groups by each unique COMBINATION of those columns

GROUP BY country, city produces one row per country+city pair - the standard way to build multi-level summaries.

GROUP BYMultiple columns

Q8. medium

What does this produce? SELECT dept, COUNT(*) FROM employees GROUP BY dept ORDER BY COUNT(*) DESC;

  1. A Departments listed from largest to smallest headcount
  2. B Departments in alphabetical order
  3. C An error - you cannot ORDER BY an aggregate
  4. D One row

Answer: A — Departments listed from largest to smallest headcount

ORDER BY runs after aggregation, so it can sort by aggregate values (or by their alias). This is the classic 'top categories' report.

ORDER BYAggregates

Q10. medium

Can HAVING be used without GROUP BY?

  1. A Yes - the whole result is treated as one group
  2. B No, GROUP BY is mandatory
  3. C Only with COUNT
  4. D It is always an error

Answer: A — Yes - the whole result is treated as one group

SELECT COUNT(*) FROM t HAVING COUNT(*) > 100 returns the count only if it exceeds 100. Rare, but valid.

HAVING

Q12. medium

What does this answer? SELECT customer_id, SUM(total) FROM orders GROUP BY customer_id HAVING SUM(total) > 10000;

  1. A Which customers have spent more than 10,000 in total
  2. B Which single orders exceed 10,000
  3. C All customers
  4. D An error

Answer: A — Which customers have spent more than 10,000 in total

The condition applies to the SUM per customer, not to individual orders. Using WHERE total > 10000 would ask a different question entirely.

HAVINGBusiness queries

Q14. medium

How would you count orders per month?

SELECT DATE_TRUNC('month', order_date) AS month, COUNT(*)
FROM orders GROUP BY 1 ORDER BY 1;
  1. A GROUP BY a date expression such as DATE_TRUNC('month', order_date) or EXTRACT(MONTH FROM order_date)
  2. B GROUP BY order_date
  3. C GROUP BY month
  4. D It is impossible

Answer: A — GROUP BY a date expression such as DATE_TRUNC('month', order_date) or EXTRACT(MONTH FROM order_date)

Grouping by the raw timestamp would create a group per second. Truncating to the month collapses them correctly. Include the year, or January 2025 and January 2026 merge.

DatesDATE_TRUNC

Q16. medium

How many rows does GROUP BY return?

  1. A One row per unique group
  2. B The same as the original table
  3. C Always one
  4. D Twice the groups

Answer: A — One row per unique group

Ten cities produce ten rows. This 'collapse' is why non-grouped, non-aggregated columns are not allowed.

GROUP BY

Q19. medium

Which query finds departments with an average salary above 50000?

  1. A SELECT dept, AVG(salary) FROM emp GROUP BY dept HAVING AVG(salary) > 50000;
  2. B SELECT dept FROM emp WHERE AVG(salary) > 50000;
  3. C SELECT dept, AVG(salary) FROM emp WHERE salary > 50000;
  4. D SELECT AVG(dept) FROM emp;

Answer: A — SELECT dept, AVG(salary) FROM emp GROUP BY dept HAVING AVG(salary) > 50000;

Option B is invalid (aggregate in WHERE). Option C is a different question - it averages only the high salaries, ignoring low earners.

HAVINGAVG

Q21. medium

What does this find? SELECT product_id FROM order_items GROUP BY product_id HAVING COUNT(DISTINCT order_id) >= 3;

  1. A Products appearing in at least 3 different orders
  2. B Products ordered 3 times total
  3. C The three most popular products
  4. D An error

Answer: A — Products appearing in at least 3 different orders

COUNT(DISTINCT order_id) counts distinct orders, not line items - so a product bought 3 times in ONE order would not qualify. Precision matters in metrics.

COUNT DISTINCTHAVING

Q23. medium

How do you show groups WITHOUT the aggregate column?

  1. A Just omit it from SELECT - GROUP BY still works: SELECT city FROM t GROUP BY city;
  2. B It is required in SELECT
  3. C Use DISTINCT instead only
  4. D Not possible

Answer: A — Just omit it from SELECT - GROUP BY still works: SELECT city FROM t GROUP BY city;

That query is effectively SELECT DISTINCT city. GROUP BY without aggregates is legal, though DISTINCT states the intent more clearly.

DISTINCTGROUP BY

Q25. medium

Which finds cities with exactly one customer?

  1. A GROUP BY city HAVING COUNT(*) = 1
  2. B WHERE COUNT(*) = 1
  3. C GROUP BY city LIMIT 1
  4. D SELECT DISTINCT city

Answer: A — GROUP BY city HAVING COUNT(*) = 1

HAVING can test for any count - equal to, greater than, or a range. Useful for finding one-off or anomalous values.

HAVING

Q27. medium

How do you get the top 3 categories by revenue?

  1. A GROUP BY category, ORDER BY SUM(revenue) DESC, LIMIT 3
  2. B HAVING TOP 3
  3. C GROUP BY category LIMIT 3
  4. D MAX(revenue, 3)

Answer: A — GROUP BY category, ORDER BY SUM(revenue) DESC, LIMIT 3

Aggregate, sort by the aggregate, then limit. Option C would return three arbitrary categories because LIMIT applies before any sorting is specified.

Top NORDER BY

Q29. medium

Which clause would you add to only show groups with no NULL values in a column?

  1. A HAVING COUNT(col) = COUNT(*)
  2. B WHERE col IS NOT NULL only
  3. C HAVING col IS NOT NULL
  4. D It is impossible

Answer: A — HAVING COUNT(col) = COUNT(*)

COUNT(col) skips NULLs, so equality with COUNT(*) means the group is complete. A neat data-quality check - option B would instead remove the NULL rows before grouping.

COUNTData quality

Q30. medium

Can you combine WHERE, GROUP BY, HAVING and ORDER BY in one query?

SELECT city, COUNT(*) AS n
FROM customers
WHERE active = 1
GROUP BY city
HAVING COUNT(*) > 5
ORDER BY n DESC;
  1. A Yes - in that exact order: WHERE (rows), GROUP BY (group), HAVING (groups), ORDER BY (sort)
  2. B No, only one filter is allowed
  3. C HAVING must come before GROUP BY
  4. D ORDER BY must come first

Answer: A — Yes - in that exact order: WHERE (rows), GROUP BY (group), HAVING (groups), ORDER BY (sort)

A full aggregate query commonly uses all four. Written order is fixed; the database executes them as WHERE, GROUP BY, HAVING, then ORDER BY - which is why each clause can only reference what already exists at its stage.

Clause orderExecution order

Hard questions (13)

Q3. hard

Why does this fail in most databases? SELECT city, name, COUNT(*) FROM customers GROUP BY city;

  1. A name is neither grouped nor aggregated - each city has many names
  2. B COUNT cannot be used with GROUP BY
  3. C city must be aggregated
  4. D It always works

Answer: A — name is neither grouped nor aggregated - each city has many names

Add name to GROUP BY (giving one row per city+name), or aggregate it: MAX(name). MySQL historically allowed this and returned an arbitrary name - a source of silently wrong reports.

GROUP BYONLY_FULL_GROUP_BY

Q5. hard

In what ORDER does the database process these clauses?

  1. A FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT
  2. B SELECT, FROM, WHERE, GROUP BY
  3. C GROUP BY, WHERE, SELECT
  4. D The order they are written

Answer: A — FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT

This single fact explains most GROUP BY rules: WHERE cannot see aggregates (they do not exist yet), and SELECT aliases are unavailable to WHERE, GROUP BY and HAVING - but ARE available to ORDER BY.

Execution order

Q9. hard

How does GROUP BY treat NULL values?

  1. A All NULLs form ONE group together
  2. B Each NULL becomes its own group
  3. C NULL rows are excluded
  4. D It errors

Answer: A — All NULLs form ONE group together

Even though NULL = NULL is not TRUE in comparisons, grouping and DISTINCT treat NULLs as the same value. Your report will show a single NULL row.

NULLGROUP BY

Q11. hard

Which query finds duplicate emails?

  1. A SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;
  2. B SELECT DISTINCT email FROM users;
  3. C SELECT email FROM users WHERE COUNT(*) > 1;
  4. D SELECT COUNT(email) FROM users;

Answer: A — SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

Group by the column and keep groups appearing more than once - the canonical duplicate-finding query, and a common interview question.

DuplicatesHAVING

Q13. hard

Can you use a column ALIAS in GROUP BY?

  1. A It depends - MySQL and PostgreSQL allow it, but the standard (and SQL Server) require the full expression
  2. B Always yes
  3. C Never
  4. D Only for numbers

Answer: A — It depends - MySQL and PostgreSQL allow it, but the standard (and SQL Server) require the full expression

For portable code, repeat the expression in GROUP BY or wrap the query in a subquery. Aliases are always safe in ORDER BY.

AliasesDialects

Q15. hard

What does GROUP BY 1 mean?

  1. A Group by the FIRST column in the SELECT list (positional reference)
  2. B Group into one group
  3. C Group by the number 1
  4. D A syntax error

Answer: A — Group by the FIRST column in the SELECT list (positional reference)

Convenient shorthand for long expressions, common in analytics. The risk is that reordering SELECT silently changes the grouping.

Positional grouping

Q17. hard

What is the difference between these two? A) WHERE status='paid' GROUP BY city B) GROUP BY city HAVING MIN(status)='paid'

  1. A A counts only paid rows per city; B keeps cities where a condition holds across the whole group
  2. B They are identical
  3. C B is invalid
  4. D A is invalid

Answer: A — A counts only paid rows per city; B keeps cities where a condition holds across the whole group

WHERE changes which rows enter each group; HAVING decides which finished groups survive. Mixing them up changes the meaning of a report entirely.

WHERE vs HAVING

Q18. hard

What does GROUP BY ROLLUP(country, city) add?

SELECT country, city, SUM(sales)
FROM t GROUP BY ROLLUP(country, city);
  1. A Subtotal rows per country plus a grand total, alongside the detail rows
  2. B Sorting by country
  3. C Nothing extra
  4. D It removes duplicates

Answer: A — Subtotal rows per country plus a grand total, alongside the detail rows

ROLLUP generates hierarchical subtotals - very useful for financial reports. The subtotal rows show NULL in the columns they aggregate over.

ROLLUPCUBEReporting

Q20. hard

Can HAVING reference a column NOT in GROUP BY or an aggregate?

  1. A No - HAVING can only use grouped columns and aggregates
  2. B Yes, any column
  3. C Only text columns
  4. D Only with a subquery

Answer: A — No - HAVING can only use grouped columns and aggregates

After grouping, individual row values no longer exist. Conditions on raw values belong in WHERE.

HAVINGRules

Q22. hard

Why might GROUP BY be slow, and what helps?

  1. A It must sort or hash all rows - an index on the grouped column, and filtering first with WHERE, both help
  2. B It is never slow
  3. C Only more RAM helps
  4. D Removing SELECT helps

Answer: A — It must sort or hash all rows - an index on the grouped column, and filtering first with WHERE, both help

Grouping a million rows requires organising them all. Reducing the input with WHERE and supporting the grouping with an index are the main levers.

PerformanceIndexes

Q24. hard

What does FILTER (WHERE ...) do on an aggregate?

SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE status='paid') AS paid
FROM orders;
  1. A Applies a condition to that ONE aggregate only - a cleaner alternative to CASE inside aggregates
  2. B Filters the whole query
  3. C Sorts the aggregate
  4. D Is invalid SQL

Answer: A — Applies a condition to that ONE aggregate only - a cleaner alternative to CASE inside aggregates

Standard SQL, supported by PostgreSQL and SQLite. Elsewhere use SUM(CASE WHEN ... THEN 1 ELSE 0 END).

FILTERConditional aggregation

Q26. hard

What is the risk of GROUP BY after a one-to-many JOIN?

  1. A Parent values are duplicated by the join, so SUM/COUNT on them is inflated
  2. B GROUP BY cannot follow a join
  3. C It removes NULLs
  4. D There is no risk

Answer: A — Parent values are duplicated by the join, so SUM/COUNT on them is inflated

Joining customers to orders repeats each customer per order. COUNT(*) then counts orders, not customers - use COUNT(DISTINCT customer_id) or aggregate before joining.

JoinsFan-out

Q28. hard

What does GROUP BY GROUPING SETS give you?

  1. A Several different groupings computed in ONE query - e.g. by country, by city, and overall
  2. B Only one grouping
  3. C A sorted output
  4. D A join

Answer: A — Several different groupings computed in ONE query - e.g. by country, by city, and overall

It avoids running three separate queries and UNIONing them. ROLLUP and CUBE are convenient shorthands for common grouping sets.

GROUPING SETSROLLUPCUBE

Practice “GROUP BY & HAVING” as a quiz