Chapter 9 Intermediate 58 Questions

Practice Questions — Aggregate Functions (COUNT, SUM, AVG, MIN, MAX)

← Back to Notes
16 Easy
18 Medium
13 Hard

Topic-Specific Questions

Question 1
Easy
Count the total number of orders.
COUNT(*).
SELECT COUNT(*) FROM orders;
Question 2
Easy
What is the total revenue (sum of all amounts)?
SUM(amount).
SELECT SUM(amount) AS total_revenue FROM orders;
Question 3
Easy
Find the smallest and largest order amounts.
MIN and MAX.
SELECT MIN(amount) AS smallest, MAX(amount) AS largest FROM orders;
Question 4
Easy
Calculate the average order amount.
AVG(amount).
SELECT AVG(amount) AS avg_order FROM orders;
Question 5
Easy
Count the number of unique cities with orders.
COUNT(DISTINCT city).
SELECT COUNT(DISTINCT city) AS unique_cities FROM orders;
Question 6
Easy
What does COUNT(discount) return on our orders table?
Excludes NULLs.
8
Question 7
Easy
Total revenue from Delivered orders only.
SUM with WHERE.
SELECT SUM(amount) FROM orders WHERE status = 'Delivered';
Question 8
Easy
How many orders were Cancelled?
COUNT(*) with WHERE.
SELECT COUNT(*) FROM orders WHERE status = 'Cancelled';
Question 9
Medium
What does this return?
SELECT AVG(discount) FROM orders;
NULLs excluded from both parts.
356.25
Question 10
Medium
What does this return?
SELECT AVG(COALESCE(discount, 0)) FROM orders;
NULLs as 0 over all 12 rows.
237.50
Question 11
Medium
Find the earliest and latest order dates.
MIN and MAX on dates.
SELECT MIN(order_date) AS earliest, MAX(order_date) AS latest FROM orders;
Question 12
Medium
Explain the difference between COUNT(*) and COUNT(column).
NULL handling.
COUNT(*) counts all rows, regardless of content. COUNT(column) counts only rows where column is NOT NULL. For a column with 10 values where 3 are NULL: COUNT(*) = 10, COUNT(column) = 7.
Question 13
Medium
Why does SUM of an all-NULL column return NULL instead of 0?
SQL standard behavior.
Because there are no values to sum, SQL returns NULL — the logical 'unknown.' Returning 0 would be an assumption. If you want 0, use COALESCE(SUM(col), 0). This convention also applies to MIN, MAX, and AVG: all-NULL input yields NULL output.
Question 14
Medium
How many unique customers placed orders in March 2026?
COUNT(DISTINCT) with date range.
SELECT COUNT(DISTINCT customer_name) AS march_customers
FROM orders
WHERE order_date BETWEEN '2026-03-01' AND '2026-03-31';
Question 15
Medium
What is the average amount of Delivered orders?
AVG with WHERE.
SELECT AVG(amount) FROM orders WHERE status = 'Delivered';
Question 16
Medium
Count how many orders each status type has (preview of GROUP BY — use conditional aggregation).
SUM(CASE WHEN ...).
SELECT
  SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) AS delivered,
  SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled,
  SUM(CASE WHEN status = 'Pending' THEN 1 ELSE 0 END) AS pending
FROM orders;
Question 17
Medium
What does this return?
SELECT MIN(customer_name), MAX(customer_name) FROM orders;
Strings sort alphabetically.
Aarav Sharma, Vikram Reddy
Question 18
Medium
Combine all Mumbai customer names into a single comma-separated string.
GROUP_CONCAT with WHERE.
SELECT GROUP_CONCAT(customer_name) AS mumbai_customers
FROM orders
WHERE city = 'Mumbai';
Question 19
Hard
Find orders with amount greater than the overall average. Return customer_name and amount.
Subquery for AVG.
SELECT customer_name, amount FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);
Question 20
Hard
Why can't you use WHERE amount > AVG(amount) directly?
Order of operations.
Because WHERE runs BEFORE aggregation. When WHERE evaluates each row, AVG has not been computed yet — the aggregate needs all rows to finish first. SQL enforces a strict order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Aggregates belong in SELECT or HAVING, not WHERE. To compare a row to an aggregate, use a subquery that computes the aggregate separately.
Question 21
Hard
What does this return?
SELECT COUNT(*), COUNT(discount), COUNT(DISTINCT city)
FROM orders
WHERE status = 'Delivered';
Filter first, then aggregate.
8, 6, 6
Question 22
Hard
What was the average order amount in February 2026?
WHERE + AVG.
SELECT AVG(amount) FROM orders
WHERE order_date BETWEEN '2026-02-01' AND '2026-02-28';
Question 23
Hard
For each Mumbai customer, show the names sorted by amount DESC combined into one string with ' > ' separator.
GROUP_CONCAT with ORDER BY and SEPARATOR.
SELECT GROUP_CONCAT(customer_name ORDER BY amount DESC SEPARATOR ' > ') AS mumbai_by_amount
FROM orders
WHERE city = 'Mumbai';
Question 24
Hard
Return the total revenue, total discount given, and net revenue (revenue minus discount), treating NULL discounts as 0.
SUM with COALESCE.
SELECT
  SUM(amount) AS gross_revenue,
  SUM(COALESCE(discount, 0)) AS total_discount,
  SUM(amount) - SUM(COALESCE(discount, 0)) AS net_revenue
FROM orders;
Question 25
Hard
In what order are WHERE, SELECT, ORDER BY, and aggregate functions evaluated?
Logical execution order.
Logical order: FROM → WHERE → GROUP BY → HAVING → SELECT (including aggregates) → ORDER BY → LIMIT. Aggregates are computed during the SELECT phase after WHERE has already filtered rows. This is why WHERE cannot reference aggregates — they do not exist yet. ORDER BY runs after aggregates, so it CAN reference aliases and aggregate results.

Mixed & Application Questions

Question 1
Easy
Get the total number of orders.
COUNT(*).
SELECT COUNT(*) FROM orders;
Question 2
Easy
What is the total amount across all orders?
SUM(amount).
SELECT SUM(amount) FROM orders;
Question 3
Easy
What is the largest order amount?
MAX.
SELECT MAX(amount) FROM orders;
Question 4
Easy
Average amount?
AVG.
SELECT AVG(amount) FROM orders;
Question 5
Easy
What is COUNT(DISTINCT status)?
Unique statuses.
3
Question 6
Easy
Count Mumbai orders.
COUNT + WHERE.
SELECT COUNT(*) FROM orders WHERE city = 'Mumbai';
Question 7
Easy
What does SUM(NULL) return?
NULL-only sum.
NULL
Question 8
Easy
Total discount given across all orders (treating NULL as 0).
SUM of COALESCE.
SELECT SUM(COALESCE(discount, 0)) FROM orders;
Question 9
Medium
Count orders from each city using conditional aggregation (show Mumbai_count, Delhi_count).
SUM(CASE WHEN city = 'Mumbai' ...).
SELECT
  SUM(CASE WHEN city = 'Mumbai' THEN 1 ELSE 0 END) AS mumbai_count,
  SUM(CASE WHEN city = 'Delhi' THEN 1 ELSE 0 END) AS delhi_count
FROM orders;
Question 10
Medium
Find the largest amount in Bengaluru.
MAX + WHERE.
SELECT MAX(amount) FROM orders WHERE city = 'Bengaluru';
Question 11
Medium
Average amount of Pending orders.
AVG + WHERE.
SELECT AVG(amount) FROM orders WHERE status = 'Pending';
Question 12
Medium
What does this return?
SELECT COUNT(discount), SUM(discount), AVG(discount) FROM orders WHERE discount IS NULL;
All rows filtered out have NULL discount, so the aggregates work on... nothing?
0, NULL, NULL
Question 13
Medium
Get the total revenue from Bengaluru and Mumbai combined.
SUM + IN.
SELECT SUM(amount) FROM orders
WHERE city IN ('Bengaluru', 'Mumbai');
Question 14
Medium
Find the range of order amounts (MAX - MIN).
MAX and MIN in same query.
SELECT MAX(amount) - MIN(amount) AS amount_range FROM orders;
Question 15
Medium
Count how many orders have a discount given.
COUNT(discount) excludes NULLs.
SELECT COUNT(discount) FROM orders;
Question 16
Medium
What does AVG(amount) return in a WHERE that filters 0 rows?
SELECT AVG(amount) FROM orders WHERE city = 'Kolkata';
No Kolkata orders.
NULL
Question 17
Hard
What is the total amount of orders placed by customers whose name starts with 'A'?
SUM + LIKE.
SELECT SUM(amount) FROM orders
WHERE customer_name LIKE 'A%';
Question 18
Hard
Find how many orders were above the overall average amount.
COUNT with subquery.
SELECT COUNT(*) FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);
Question 19
Hard
For Delivered orders, show the total revenue, total discount (NULL as 0), and net revenue.
Combine SUM with WHERE and COALESCE.
SELECT
  SUM(amount) AS gross,
  SUM(COALESCE(discount, 0)) AS total_discount,
  SUM(amount) - SUM(COALESCE(discount, 0)) AS net
FROM orders
WHERE status = 'Delivered';
Question 20
Hard
Find the customer name with the largest order amount. (Hint: use a subquery to find MAX, then match.)
Subquery in WHERE.
SELECT customer_name, amount FROM orders
WHERE amount = (SELECT MAX(amount) FROM orders);
Question 21
Hard
What does this return?
SELECT COUNT(*) - COUNT(discount) FROM orders;
Total rows minus non-NULL discount rows.
4
Question 22
Hard
Create a string with all Delhi customer names separated by ' & ', ordered by order_date ASC.
GROUP_CONCAT with ORDER BY and SEPARATOR.
SELECT GROUP_CONCAT(customer_name ORDER BY order_date ASC SEPARATOR ' & ') AS delhi_customers
FROM orders
WHERE city = 'Delhi';

Multiple Choice Questions

MCQ 1
Which function counts the total number of rows in a table?
  • A. SUM(*)
  • B. COUNT(*)
  • C. TOTAL()
  • D. ROWS()
Answer: B
B is correct. COUNT(*) counts all rows, including those with NULLs.
MCQ 2
What does SUM(salary) return if all salary values are NULL?
  • A. 0
  • B. NULL
  • C. Error
  • D. Empty string
Answer: B
B is correct. SUM of all NULLs (or no rows) returns NULL. Wrap with COALESCE(SUM(col), 0) if you want 0.
MCQ 3
Which function gives the largest value in a column?
  • A. MAX()
  • B. MAXIMUM()
  • C. TOP()
  • D. LARGEST()
Answer: A
A is correct. MAX(column) returns the largest value. Works on numbers, dates, and strings (alphabetically).
MCQ 4
For a column with values [10, 20, NULL, 30], what does AVG return?
  • A. 15 (60 / 4)
  • B. 20 (60 / 3)
  • C. NULL
  • D. 30
Answer: B
B is correct. AVG skips NULLs in both numerator and denominator. Sum: 60. Count of non-NULL: 3. AVG: 60/3 = 20.
MCQ 5
Can aggregate functions be used in the WHERE clause?
  • A. Yes, always
  • B. No, WHERE runs before aggregation
  • C. Only COUNT can be in WHERE
  • D. Only if GROUP BY is used
Answer: B
B is correct. WHERE filters rows before aggregation, so aggregates don't exist yet when WHERE runs. Use HAVING (after GROUP BY) or a subquery instead.
MCQ 6
Which counts unique values in a column?
  • A. COUNT(column)
  • B. COUNT(DISTINCT column)
  • C. UNIQUE COUNT(column)
  • D. COUNT UNIQUE column
Answer: B
B is correct. COUNT(DISTINCT col) returns the count of unique non-NULL values.
MCQ 7
MIN works on which types?
  • A. Numbers only
  • B. Dates only
  • C. Numbers, dates, and strings
  • D. Only numeric and Boolean
Answer: C
C is correct. MIN/MAX work on any orderable type. For strings, alphabetical order; for dates, chronological; for numbers, numeric.
MCQ 8
What is the difference between COUNT(*) and COUNT(1)?
  • A. COUNT(*) is faster
  • B. COUNT(1) is faster
  • C. They are functionally identical in modern MySQL
  • D. COUNT(1) returns 1, COUNT(*) returns the row count
Answer: C
C is correct. Both count all rows. Modern optimizers treat them the same. Old myth says COUNT(1) is faster — it is not. Use COUNT(*) — it's idiomatic SQL.
MCQ 9
For [10, 20, NULL], what does AVG(COALESCE(col, 0)) return?
  • A. 15 (30/2)
  • B. 10 (30/3)
  • C. NULL
  • D. 20
Answer: B
B is correct. COALESCE turns NULL into 0. Values: [10, 20, 0]. Sum: 30. Count: 3. AVG: 30/3 = 10.
MCQ 10
What does MIN(customer_name) return?
  • A. Shortest customer name
  • B. Alphabetically first customer name
  • C. NULL always for string columns
  • D. Error — MIN doesn't work on strings
Answer: B
B is correct. For strings, MIN returns the alphabetically first value. In our data, 'Aarav Sharma' is first.
MCQ 11
Which query finds the largest order amount?
  • A. SELECT TOP(amount) FROM orders;
  • B. SELECT MAX(amount) FROM orders;
  • C. SELECT amount FROM orders LIMIT 1 DESC;
  • D. SELECT GREATEST(amount) FROM orders;
Answer: B
B is correct. MAX is the SQL standard. GREATEST exists but takes multiple arguments (for comparing across columns), not an aggregate.
MCQ 12
What does SUM(CASE WHEN status='Delivered' THEN 1 ELSE 0 END) compute?
  • A. Total amount of delivered orders
  • B. Number of delivered orders
  • C. Percentage of delivered orders
  • D. Nothing useful
Answer: B
B is correct. Each matching row contributes 1, others contribute 0. SUM gives the count. This is 'conditional aggregation' and is common in pre-GROUP BY SQL.
MCQ 13
Why can't this query work? SELECT name FROM employees WHERE salary = MAX(salary);
  • A. Syntax error in MAX
  • B. WHERE runs before aggregation, so MAX is not yet computed
  • C. You can't compare salary to itself
  • D. MAX is reserved in WHERE
Answer: B
B is correct. WHERE executes before aggregates. Fix: WHERE salary = (SELECT MAX(salary) FROM employees).
MCQ 14
GROUP_CONCAT is available in which database?
  • A. MySQL
  • B. PostgreSQL
  • C. SQL Server
  • D. All of the above
Answer: A
A is correct. GROUP_CONCAT is MySQL-specific. PostgreSQL and SQL Server use STRING_AGG(col, separator). Oracle uses LISTAGG.
MCQ 15
For column with [100, 200, NULL, 200, 300], what are COUNT(*), COUNT(col), COUNT(DISTINCT col)?
  • A. 5, 4, 4
  • B. 5, 4, 3
  • C. 4, 4, 3
  • D. 5, 5, 3
Answer: B
B is correct. COUNT(*) = 5 all rows. COUNT(col) = 4 non-NULL. COUNT(DISTINCT col) = 3 unique non-NULL (100, 200, 300 — 200 is duplicate).
MCQ 16
A table has 1000 rows; half have NULL in the 'email' column. What does AVG(LENGTH(email)) compute?
  • A. Total email length across all 1000 rows divided by 1000
  • B. Total length of non-NULL emails divided by 500
  • C. Always NULL
  • D. Error
Answer: B
B is correct. LENGTH(NULL) = NULL. AVG ignores NULLs. So it averages the lengths of the 500 non-NULL emails.
MCQ 17
What does SUM(NULL) + 5 return?
  • A. 5
  • B. NULL
  • C. 0
  • D. Error
Answer: B
B is correct. SUM of no rows or all NULLs = NULL. NULL + 5 = NULL (arithmetic propagates NULL). Use COALESCE to guard.
MCQ 18
In SQL's logical execution order, when do aggregates get computed?
  • A. Before WHERE
  • B. After WHERE, during SELECT
  • C. After ORDER BY
  • D. Before FROM
Answer: B
B is correct. Order: FROM → WHERE → GROUP BY → HAVING → SELECT (aggregates here) → ORDER BY → LIMIT. That is why HAVING can see aggregates but WHERE cannot.
MCQ 19
COUNT(*) - COUNT(col) gives what?
  • A. Total rows
  • B. Non-NULL count
  • C. Number of NULLs in that column
  • D. Duplicate count
Answer: C
C is correct. COUNT(*) counts all rows, COUNT(col) counts non-NULL rows, so their difference is the NULL count. Useful trick.
MCQ 20
What does this return? SELECT AVG(amount) FROM orders WHERE city = 'NoSuchCity';
  • A. 0
  • B. NULL
  • C. Empty set (no row)
  • D. Error
Answer: B
B is correct. Aggregates always return exactly one row (without GROUP BY). With no matching rows, AVG returns NULL. COUNT(*) would be 0 — different behavior.

Coding Challenges

Challenge 1: Business KPI Summary

Easy
Return one row showing: total orders, total revenue, average order value, smallest order, largest order.
Sample Input
orders table.
Sample Output
12, 44500.00, 3708.33, 1200.00, 8900.00
Single query.
SELECT
  COUNT(*) AS total_orders,
  SUM(amount) AS total_revenue,
  AVG(amount) AS avg_order_value,
  MIN(amount) AS smallest_order,
  MAX(amount) AS largest_order
FROM orders;

Challenge 2: Delivered Orders Summary

Easy
Return total count, total revenue, and average amount but only for orders where status = 'Delivered'.
Sample Input
orders table.
Sample Output
8, 27600.00, 3450.00
WHERE + aggregates.
SELECT
  COUNT(*) AS delivered_count,
  SUM(amount) AS delivered_revenue,
  AVG(amount) AS avg_delivered
FROM orders
WHERE status = 'Delivered';

Challenge 3: NULL-Safe Averages

Medium
For the discount column (which has 4 NULLs), compute two averages: (a) AVG ignoring NULLs, (b) AVG treating NULLs as 0. Show both.
Sample Input
orders table.
Sample Output
excluding_nulls: 356.25, treating_null_as_zero: 237.50
Use COALESCE for the second.
SELECT
  AVG(discount) AS excluding_nulls,
  AVG(COALESCE(discount, 0)) AS treating_null_as_zero
FROM orders;

Challenge 4: Order Status Counts

Medium
Count how many orders are in each status using conditional aggregation — return columns 'delivered', 'cancelled', 'pending' in a single row.
Sample Input
orders table.
Sample Output
8, 2, 2
SUM(CASE WHEN ...). Do not use GROUP BY.
SELECT
  SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) AS delivered,
  SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled,
  SUM(CASE WHEN status = 'Pending' THEN 1 ELSE 0 END) AS pending
FROM orders;

Challenge 5: Find Customers Above Average Spend

Medium
List all customers who placed an order larger than the overall average amount.
Sample Input
orders table. Overall average = 3708.33.
Sample Output
Rohan Gupta, Vikram Reddy, Karan Mehta, Aditya Rao, Diya Nair (5 rows)
Use a subquery.
SELECT customer_name, amount FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders)
ORDER BY amount DESC;

Challenge 6: Monthly Revenue

Medium
Compute the total revenue for each month (January, February, March, April 2026) using conditional aggregation.
Sample Input
orders table.
Sample Output
jan: 4300 (2500+1800), feb: 13300 (5600+3200+4500), mar: 22700 (1200+6700+2100+3800+8900), apr: 4200 (2700+1500)
SUM(CASE WHEN MONTH(order_date) = 1...).
SELECT
  SUM(CASE WHEN MONTH(order_date) = 1 THEN amount ELSE 0 END) AS jan,
  SUM(CASE WHEN MONTH(order_date) = 2 THEN amount ELSE 0 END) AS feb,
  SUM(CASE WHEN MONTH(order_date) = 3 THEN amount ELSE 0 END) AS mar,
  SUM(CASE WHEN MONTH(order_date) = 4 THEN amount ELSE 0 END) AS apr
FROM orders;

Challenge 7: Concatenate Top Mumbai Customers

Hard
Return a single string with Mumbai customer names sorted by order amount DESC, joined with ' -> '.
Sample Input
orders table.
Sample Output
'Karan Mehta -> Ananya Singh -> Arjun Desai -> Aarav Sharma'
GROUP_CONCAT with ORDER BY and SEPARATOR.
SELECT GROUP_CONCAT(customer_name ORDER BY amount DESC SEPARATOR ' -> ') AS mumbai_ranking
FROM orders
WHERE city = 'Mumbai';

Challenge 8: Revenue Summary With Net Amount

Hard
Return a single row showing: gross revenue (SUM amount), total discount (NULLs as 0), net revenue (gross minus discount), average net per order, number of orders with discount given.
Sample Input
orders table.
Sample Output
gross 44500, total_discount 2850, net 41650, avg_net 3470.83, discount_given_count 8
Combine SUM, AVG, COUNT, COALESCE.
SELECT
  SUM(amount) AS gross,
  SUM(COALESCE(discount, 0)) AS total_discount,
  SUM(amount) - SUM(COALESCE(discount, 0)) AS net,
  (SUM(amount) - SUM(COALESCE(discount, 0))) / COUNT(*) AS avg_net,
  COUNT(discount) AS discount_given_count
FROM orders;

Need to Review the Concepts?

Go back to the detailed notes for this chapter.

Read Chapter Notes

Want to master SQL and databases with a mentor?

Explore our MySQL Masterclass