🎓 Velnov

🗄️ SQL · Topic 6 of 20

Aggregate Functions — SQL Questions with Answers

30 practice questions on Aggregate Functions, 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.

3 easy · 15 medium · 12 hard · Part of SQL: Databases from basics to window functions - 20 topics.

Key concepts in this topic

AggregatesSummaryCOUNTNULLCOUNT DISTINCTSUMCOALESCEAVGMINMAXGROUP BYHAVINGExecution orderROUNDWHERECASEConditional aggregationExpressionsSubqueriesNested aggregatesMythsGotchasDatesData types

Jump to: Easy · Medium · Hard

Easy questions (3)

Q1. easy

What is an aggregate function?

  1. A A function that combines MANY rows into a single summary value
  2. B A function that runs per row
  3. C A way to join tables
  4. D A sorting method

Answer: A — A function that combines MANY rows into a single summary value

COUNT, SUM, AVG, MIN and MAX collapse a set of rows into one result - the foundation of every report and dashboard.

AggregatesSummary

Q2. easy

Which function counts rows?

SELECT COUNT(*) FROM orders;
  1. A COUNT()
  2. B TOTAL()
  3. C ROWS()
  4. D NUMBER()

Answer: A — COUNT()

COUNT(*) counts all rows; COUNT(column) counts non-NULL values; COUNT(DISTINCT column) counts unique non-NULL values.

COUNT

Q5. easy

Which function adds up numeric values?

  1. A SUM()
  2. B TOTAL()
  3. C ADD()
  4. D PLUS()

Answer: A — SUM()

SUM works on numeric columns and ignores NULLs. If every value is NULL (or there are no rows), SUM returns NULL, not 0.

SUM

Medium questions (15)

Q3. medium

What is the difference between COUNT(*) and COUNT(column)?

  1. A COUNT(*) counts every row; COUNT(column) skips rows where that column is NULL
  2. B They are identical
  3. C COUNT(column) counts columns
  4. D COUNT(*) ignores NULLs

Answer: A — COUNT(*) counts every row; COUNT(column) skips rows where that column is NULL

Comparing the two reveals data completeness: COUNT(*) = 100 with COUNT(phone) = 60 means 40 customers have no phone number.

COUNTNULL

Q4. medium

What does COUNT(DISTINCT city) return?

SELECT COUNT(DISTINCT customer_id) AS unique_buyers FROM orders;
  1. A The number of unique non-NULL cities
  2. B The total row count
  3. C A list of cities
  4. D An error

Answer: A — The number of unique non-NULL cities

DISTINCT inside an aggregate deduplicates before counting - the standard way to answer 'how many different X do we have?'.

COUNT DISTINCT

Q7. medium

How does AVG treat NULL values?

  1. A It ignores them - dividing only by the count of non-NULL values
  2. B It treats them as 0
  3. C It returns NULL if any value is NULL
  4. D It errors

Answer: A — It ignores them - dividing only by the count of non-NULL values

For 10, 20, NULL the average is 15, not 10. If missing values should count as zero, write AVG(COALESCE(score, 0)).

  • This silently changes report numbers - know which behaviour you want.

AVGNULL

Q8. medium

What do MIN() and MAX() do?

  1. A Return the smallest and largest value - they work on numbers, dates and text
  2. B Only work on numbers
  3. C Return the first and last row
  4. D Count rows

Answer: A — Return the smallest and largest value - they work on numbers, dates and text

MAX(order_date) gives the most recent order; MIN(name) gives the alphabetically first name. Both ignore NULLs.

MINMAX

Q10. medium

Can you use an aggregate in the WHERE clause? WHERE COUNT(*) > 5

  1. A No - aggregates are computed after WHERE; use HAVING instead
  2. B Yes, always
  3. C Only with COUNT
  4. D Only in subqueries

Answer: A — No - aggregates are computed after WHERE; use HAVING instead

Execution order is FROM, WHERE, GROUP BY, HAVING. WHERE filters raw rows before any aggregation exists; HAVING filters the aggregated groups.

HAVINGExecution order

Q11. medium

What does this return? SELECT AVG(price) FROM products;

  1. A One row with the average price across all products
  2. B One row per product
  3. C The sum of prices
  4. D An error without GROUP BY

Answer: A — One row with the average price across all products

An aggregate without GROUP BY treats the entire result set as one group, always returning exactly one row.

AVGAggregates

Q13. medium

Which query counts orders over 1000?

  1. A SELECT COUNT(*) FROM orders WHERE total > 1000;
  2. B SELECT COUNT(total > 1000) FROM orders;
  3. C SELECT COUNT(*) HAVING total > 1000;
  4. D SELECT COUNT(WHERE total > 1000);

Answer: A — SELECT COUNT(*) FROM orders WHERE total > 1000;

Filter the rows with WHERE, then count what remains. This is more efficient than aggregating everything and filtering afterwards.

COUNTWHERE

Q15. medium

What does SUM(quantity * price) compute?

  1. A The total value - each row's product is calculated first, then summed
  2. B The sum of quantities times the sum of prices
  3. C An error
  4. D The average value

Answer: A — The total value - each row's product is calculated first, then summed

Expressions inside an aggregate are evaluated per row. Note SUM(a) * SUM(b) would give a completely different (and usually wrong) answer.

SUMExpressions

Q17. medium

What does COUNT(1) do compared with COUNT(*)?

  1. A Exactly the same thing - modern optimisers treat them identically
  2. B COUNT(1) is much faster
  3. C COUNT(1) counts only the first column
  4. D COUNT(1) returns 1

Answer: A — Exactly the same thing - modern optimisers treat them identically

An old performance myth. Both count rows; use COUNT(*) as it states the intent clearly.

COUNTMyths

Q19. medium

How do you find the most recent date in a table?

  1. A SELECT MAX(created_at) FROM t;
  2. B SELECT LAST(created_at) FROM t;
  3. C SELECT created_at ORDER BY DESC;
  4. D SELECT NEWEST(created_at);

Answer: A — SELECT MAX(created_at) FROM t;

MAX works on dates because they have a natural order. To get the whole row of that date, use ORDER BY created_at DESC LIMIT 1.

MAXDates

Q21. medium

Which aggregate finds the cheapest product's PRICE?

  1. A MIN(price)
  2. B FIRST(price)
  3. C LOW(price)
  4. D BOTTOM(price)

Answer: A — MIN(price)

MIN returns the value only. For the product's name too, either group appropriately or use ORDER BY price ASC LIMIT 1.

MIN

Q24. medium

What does this return if orders is empty? SELECT COUNT(*) FROM orders;

  1. A 0
  2. B NULL
  3. C No rows
  4. D An error

Answer: A — 0

COUNT always returns a number, so an empty table gives 0. Contrast with SUM, MIN, MAX and AVG, which return NULL when there is nothing to aggregate.

COUNTEmpty tables

Q26. medium

Which is faster for checking whether any matching row exists?

-- prefer:
IF EXISTS (SELECT 1 FROM orders WHERE customer_id = 5)
  1. A EXISTS - it stops at the first match, while COUNT(*) scans everything
  2. B COUNT(*) > 0
  3. C They are identical
  4. D SUM(1)

Answer: A — EXISTS - it stops at the first match, while COUNT(*) scans everything

Counting all matches just to compare with zero is wasted work on large tables. EXISTS short-circuits.

EXISTSPerformance

Q28. medium

What does this compute? SELECT MAX(marks) - MIN(marks) FROM students;

  1. A The RANGE - the spread between the highest and lowest score
  2. B The average
  3. C The count
  4. D An error

Answer: A — The RANGE - the spread between the highest and lowest score

Aggregates can be combined in expressions, since each returns a single value. Useful for quick data-quality checks.

MINMAXExpressions

Q30. medium

Which query answers 'what is our highest single order value'?

  1. A SELECT MAX(total) FROM orders;
  2. B SELECT SUM(total) FROM orders;
  3. C SELECT COUNT(total) FROM orders;
  4. D SELECT total FROM orders;

Answer: A — SELECT MAX(total) FROM orders;

MAX gives the largest single value; SUM would give total revenue and COUNT the number of orders. Choosing the right aggregate is choosing the right question.

MAXBusiness questions

Hard questions (12)

Q6. hard

What does SUM(amount) return when the table has NO matching rows?

SELECT COALESCE(SUM(amount), 0) AS total FROM orders WHERE 1=0;
  1. A NULL - not 0
  2. B 0
  3. C An empty string
  4. D An error

Answer: A — NULL - not 0

A frequent surprise in reports. Wrap it for a numeric default: COALESCE(SUM(amount), 0). Note COUNT does return 0 in the same situation.

SUMNULLCOALESCE

Q9. hard

Why does this fail? SELECT name, MAX(marks) FROM students;

SELECT name, marks FROM students ORDER BY marks DESC LIMIT 1;
  1. A name is not aggregated and not grouped - the database cannot know WHICH name to show
  2. B MAX only works with GROUP BY
  3. C name must be numeric
  4. D It always works

Answer: A — name is not aggregated and not grouped - the database cannot know WHICH name to show

MAX collapses many rows to one value, but name has many possible values. Either group by name, or use ORDER BY marks DESC LIMIT 1 to get the whole top row. (MySQL may allow this and return an arbitrary name - a well-known trap.)

GROUP BYAggregates

Q12. hard

How do you round an average to 2 decimal places?

SELECT ROUND(AVG(price), 2) AS avg_price FROM products;
  1. A ROUND(AVG(price), 2)
  2. B AVG(ROUND(price, 2))
  3. C AVG(price).round(2)
  4. D ROUND(price, 2) AS AVG

Answer: A — ROUND(AVG(price), 2)

Aggregate first, then round the single result. Option B rounds each row before averaging, which can give a slightly different figure.

ROUNDAVG

Q14. hard

What is conditional aggregation?

SELECT
  COUNT(*) AS total_orders,
  SUM(CASE WHEN status='paid' THEN total ELSE 0 END) AS paid_value,
  AVG(CASE WHEN status='paid' THEN total END)      AS avg_paid
FROM orders;
  1. A Using CASE inside an aggregate to compute several different metrics in one query
  2. B Aggregating with an IF statement outside SQL
  3. C Filtering with HAVING
  4. D Using two queries

Answer: A — Using CASE inside an aggregate to compute several different metrics in one query

It produces cross-tab style summaries in a single scan of the table - far faster than running several separate queries.

CASEConditional aggregation

Q16. hard

Can aggregates be nested, like MAX(COUNT(*))?

SELECT MAX(cnt) FROM (
  SELECT city, COUNT(*) AS cnt FROM customers GROUP BY city
) AS t;
  1. A Not directly - you need a subquery or CTE to aggregate an aggregate
  2. B Yes, always
  3. C Only with GROUP BY
  4. D Only MAX can nest

Answer: A — Not directly - you need a subquery or CTE to aggregate an aggregate

Compute the counts in an inner query, then aggregate that result. (Some databases allow MAX(COUNT(*)) with GROUP BY, but the subquery form is clearer and portable.)

SubqueriesNested aggregates

Q18. hard

What does this compute? SELECT SUM(price) / COUNT(*) FROM products;

  1. A The average price - but it counts rows with NULL prices, unlike AVG(price)
  2. B Exactly the same as AVG(price)
  3. C The total price
  4. D An error

Answer: A — The average price - but it counts rows with NULL prices, unlike AVG(price)

SUM skips NULLs while COUNT(*) counts every row, so with missing prices the result is LOWER than AVG(price). A subtle reporting discrepancy worth knowing.

AVGNULLGotchas

Q20. hard

What is the difference between COUNT(DISTINCT x) and COUNT(x)?

  1. A DISTINCT counts unique values; without it, every non-NULL occurrence is counted
  2. B They are identical
  3. C DISTINCT includes NULLs
  4. D COUNT(x) counts uniques

Answer: A — DISTINCT counts unique values; without it, every non-NULL occurrence is counted

In an orders table, COUNT(customer_id) = number of orders, while COUNT(DISTINCT customer_id) = number of customers who ordered. Two very different metrics.

COUNT DISTINCT

Q22. hard

Do aggregates work on text columns?

  1. A COUNT, MIN and MAX do (alphabetical order); SUM and AVG do not
  2. B None work on text
  3. C All work on text
  4. D Only COUNT works

Answer: A — COUNT, MIN and MAX do (alphabetical order); SUM and AVG do not

MAX(name) returns the alphabetically last name. Attempting SUM on text either errors or silently coerces to 0 depending on the database.

MINMAXData types

Q23. hard

What does STRING_AGG / GROUP_CONCAT do?

SELECT product_id, STRING_AGG(tag, ', ') AS tags
FROM product_tags GROUP BY product_id;
  1. A Combines values from many rows into ONE delimited string
  2. B Counts strings
  3. C Splits a string
  4. D Sorts strings

Answer: A — Combines values from many rows into ONE delimited string

STRING_AGG(name, ', ') in PostgreSQL/SQL Server, GROUP_CONCAT(name) in MySQL, LISTAGG in Oracle - perfect for listing all tags of a product on one line.

STRING_AGGGROUP_CONCAT

Q25. hard

How do you compute a percentage of the total in one query?

  1. A Divide by a subquery: amount * 100.0 / (SELECT SUM(amount) FROM t)
  2. B amount / SUM(amount)
  3. C PERCENT(amount)
  4. D It requires two queries

Answer: A — Divide by a subquery: amount * 100.0 / (SELECT SUM(amount) FROM t)

The subquery computes the grand total once. A window function does this even more elegantly: amount * 100.0 / SUM(amount) OVER ().

SubqueriesWindow functions

Q27. hard

What is the median, and why is it awkward in standard SQL?

  1. A The middle value - there is no simple MEDIAN() in most databases, so it needs window functions or PERCENTILE_CONT
  2. B The same as AVG
  3. C A built-in aggregate everywhere
  4. D Impossible to compute

Answer: A — The middle value - there is no simple MEDIAN() in most databases, so it needs window functions or PERCENTILE_CONT

PostgreSQL and Oracle offer PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x). Elsewhere you rank rows and pick the middle one. Medians resist outliers better than averages.

PercentilesStatistics

Q29. hard

Why can aggregates give wrong totals after a JOIN?

  1. A A one-to-many join duplicates parent rows, so their values get summed multiple times
  2. B Aggregates never work with joins
  3. C Joins remove NULLs
  4. D It cannot happen

Answer: A — A one-to-many join duplicates parent rows, so their values get summed multiple times

Joining orders to order_items repeats each order once per item, inflating SUM(order.total). Aggregate in a subquery first, or sum the item-level values instead.

  • A classic, hard-to-spot reporting bug.

JoinsFan-outAggregates

Practice “Aggregate Functions” as a quiz