Chapter 10 Intermediate 60 Questions

Practice Questions — GROUP BY and HAVING

← Back to Notes
18 Easy
17 Medium
11 Hard

Topic-Specific Questions

Question 1
Easy
Total sales per region.
GROUP BY region.
SELECT region, SUM(amount) FROM sales GROUP BY region;
Question 2
Easy
Count the number of sales per salesperson.
GROUP BY salesperson.
SELECT salesperson, COUNT(*) FROM sales GROUP BY salesperson;
Question 3
Easy
Average sale amount per product.
GROUP BY product.
SELECT product, AVG(amount) FROM sales GROUP BY product;
Question 4
Easy
Number of sales per region.
COUNT(*) per region.
SELECT region, COUNT(*) FROM sales GROUP BY region;
Question 5
Easy
Find regions with total sales above 150000.
HAVING with SUM.
SELECT region, SUM(amount) FROM sales
GROUP BY region
HAVING SUM(amount) > 150000;
Question 6
Easy
Top 3 regions by total sales.
GROUP BY + ORDER BY + LIMIT.
SELECT region, SUM(amount) AS total FROM sales
GROUP BY region
ORDER BY total DESC
LIMIT 3;
Question 7
Easy
Salespeople with more than 2 sales.
HAVING COUNT(*).
SELECT salesperson, COUNT(*) FROM sales
GROUP BY salesperson
HAVING COUNT(*) > 2;
Question 8
Easy
How many groups does GROUP BY region, product produce?
Unique (region, product) pairs.
11 rows
Question 9
Easy
What is the difference between WHERE and HAVING?
When each runs.
WHERE filters rows BEFORE aggregation. HAVING filters groups AFTER aggregation. WHERE cannot use aggregate functions (SUM, COUNT, etc.) because the aggregates don't exist yet. HAVING can use aggregates because grouping is already done.
Question 10
Easy
Why must non-aggregated columns in SELECT appear in GROUP BY?
Ambiguity without it.
If a column is not aggregated and not in GROUP BY, the database does not know which value to return for each group. A group of 5 rows may have 5 different values — which one should be picked? Standard SQL requires every SELECT column to be aggregated or grouped. MySQL's default strict mode enforces this with ONLY_FULL_GROUP_BY. Older lenient mode silently returned an arbitrary value — a dangerous bug.
Question 11
Medium
Total revenue per salesperson, only for sales above 30000 (filter rows first).
WHERE + GROUP BY.
SELECT salesperson, SUM(amount) FROM sales
WHERE amount > 30000
GROUP BY salesperson;
Question 12
Medium
Total sales per region, but only include regions where the number of sales is 4 or more.
HAVING COUNT(*) >= 4.
SELECT region, SUM(amount) AS total, COUNT(*) AS n FROM sales
GROUP BY region
HAVING COUNT(*) >= 4;
Question 13
Medium
Average sale amount per region for Laptops only.
WHERE product = 'Laptop' first.
SELECT region, AVG(amount) AS avg_laptop_price FROM sales
WHERE product = 'Laptop'
GROUP BY region;
Question 14
Medium
Show region and product combinations with total revenue above 100000.
GROUP BY 2 cols + HAVING SUM.
SELECT region, product, SUM(amount) AS total FROM sales
GROUP BY region, product
HAVING SUM(amount) > 100000;
Question 15
Medium
How many rows does this return?
SELECT region FROM sales GROUP BY region HAVING COUNT(*) > 10;
Any region with > 10 sales?
0 rows
Question 16
Medium
What is the logical execution order of a SQL query with WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT?
Seven phases.
Logical order: (1) FROM — identify source tables. (2) WHERE — filter individual rows. (3) GROUP BY — form groups. (4) HAVING — filter groups. (5) SELECT — compute output columns and aggregates. (6) ORDER BY — sort the result. (7) LIMIT — take first n rows.
Question 17
Medium
Monthly revenue for 2026 (group by MONTH of sale_date).
GROUP BY MONTH(sale_date).
SELECT MONTH(sale_date) AS month, SUM(amount) AS revenue FROM sales
WHERE YEAR(sale_date) = 2026
GROUP BY MONTH(sale_date)
ORDER BY month;
Question 18
Medium
Show salesperson and total revenue; include only those with total above 90000.
HAVING SUM.
SELECT salesperson, SUM(amount) AS total FROM sales
GROUP BY salesperson
HAVING SUM(amount) > 90000;
Question 19
Medium
For each region, count unique products sold.
COUNT(DISTINCT product) per region.
SELECT region, COUNT(DISTINCT product) AS unique_products FROM sales
GROUP BY region;
Question 20
Medium
What does this return?
SELECT COUNT(*) FROM sales GROUP BY region;
One row per region.
4 rows: 5, 4, 3, 3
Question 21
Hard
Find regions where the average sale is above 40000 AND total sales count is at least 3.
HAVING with two conditions.
SELECT region, AVG(amount) AS avg_sale, COUNT(*) AS n FROM sales
GROUP BY region
HAVING AVG(amount) > 40000 AND COUNT(*) >= 3;
Question 22
Hard
Find the salesperson with the highest total sales.
GROUP BY + ORDER BY DESC + LIMIT 1.
SELECT salesperson, SUM(amount) AS total FROM sales
GROUP BY salesperson
ORDER BY total DESC
LIMIT 1;
Question 23
Hard
For products that have more than 3 sales, show the product and its average price.
GROUP BY product, HAVING COUNT(*) > 3.
SELECT product, AVG(amount) AS avg_price, COUNT(*) AS n FROM sales
GROUP BY product
HAVING COUNT(*) > 3;
Question 24
Hard
What does this return?
SELECT region, SUM(amount)
FROM sales
WHERE product = 'Laptop'
GROUP BY region
HAVING SUM(amount) > 100000;
Laptop sales per region, filtered by sum.
North 115000, South 115000, West 127000 — 3 rows
Question 25
Hard
Can you get 'top 2 salespeople per region' using just GROUP BY? Why or why not?
GROUP BY collapses groups.
No. GROUP BY gives you one row per group, so you can get the top salesperson per region (by using MAX or by combining ORDER BY with LIMIT on the grouped result) but not the top N per group. LIMIT applies to the final result, not within groups. For top N per group, you need window functions — ROW_NUMBER() OVER (PARTITION BY region ORDER BY total_sales DESC) with a filter WHERE rn <= 2. This is covered in chapter 18 and is a very common interview question.
Question 26
Hard
Total sales per region, adding a grand total row. Use WITH ROLLUP.
GROUP BY region WITH ROLLUP.
SELECT region, SUM(amount) FROM sales
GROUP BY region WITH ROLLUP;

Mixed & Application Questions

Question 1
Easy
Number of sales per product.
GROUP BY product.
SELECT product, COUNT(*) FROM sales GROUP BY product;
Question 2
Easy
Average sale per region.
AVG + GROUP BY.
SELECT region, AVG(amount) FROM sales GROUP BY region;
Question 3
Easy
Largest sale per region.
MAX + GROUP BY.
SELECT region, MAX(amount) FROM sales GROUP BY region;
Question 4
Easy
Revenue per salesperson, sorted by revenue DESC.
GROUP BY salesperson + ORDER BY.
SELECT salesperson, SUM(amount) AS total FROM sales
GROUP BY salesperson
ORDER BY total DESC;
Question 5
Easy
Find the salesperson with just 1 sale.
HAVING COUNT(*) = 1.
SELECT salesperson FROM sales
GROUP BY salesperson
HAVING COUNT(*) = 1;
Question 6
Easy
How many rows?
SELECT product FROM sales GROUP BY product;
Distinct products.
3 rows
Question 7
Easy
Total revenue per product.
GROUP BY product with SUM.
SELECT product, SUM(amount) FROM sales GROUP BY product;
Question 8
Easy
Is this valid?
SELECT region, MAX(amount), COUNT(*) FROM sales GROUP BY region;
Multiple aggregates allowed?
Yes — valid. Returns 4 rows.
Question 9
Medium
Find products with total revenue above 100000.
GROUP BY product, HAVING SUM > 100000.
SELECT product, SUM(amount) FROM sales
GROUP BY product
HAVING SUM(amount) > 100000;
Question 10
Medium
Revenue per (salesperson, product) combination.
GROUP BY two columns.
SELECT salesperson, product, SUM(amount) FROM sales
GROUP BY salesperson, product;
Question 11
Medium
Count sales per salesperson, but only where the sale amount is above 50000 (filter rows first).
WHERE + GROUP BY + COUNT.
SELECT salesperson, COUNT(*) AS big_sales FROM sales
WHERE amount > 50000
GROUP BY salesperson;
Question 12
Medium
For each region, average sale amount but only show regions where that average is between 30000 and 50000.
HAVING with range.
SELECT region, AVG(amount) AS avg_sale FROM sales
GROUP BY region
HAVING AVG(amount) BETWEEN 30000 AND 50000;
Question 13
Medium
What is the output row count?
SELECT region, product, SUM(amount) FROM sales GROUP BY region, product;
Unique (region, product) pairs.
11 rows
Question 14
Medium
Show each region with its highest single sale and the product that made it.
MAX alone does NOT give you the corresponding product. Use a subquery.
SELECT region, product, amount FROM sales
WHERE (region, amount) IN (
  SELECT region, MAX(amount) FROM sales GROUP BY region
);
Question 15
Medium
List products with at least 2 unique salespeople selling them.
HAVING COUNT(DISTINCT salesperson).
SELECT product, COUNT(DISTINCT salesperson) AS unique_sellers FROM sales
GROUP BY product
HAVING COUNT(DISTINCT salesperson) >= 2;
Question 16
Hard
Find regions with more than 1 product type sold AND total revenue > 150000.
HAVING with AND of two aggregates.
SELECT region, COUNT(DISTINCT product) AS products, SUM(amount) AS total FROM sales
GROUP BY region
HAVING COUNT(DISTINCT product) > 1 AND SUM(amount) > 150000;
Question 17
Hard
Monthly revenue for Laptops only.
WHERE product = 'Laptop' + GROUP BY month.
SELECT MONTH(sale_date) AS month, SUM(amount) FROM sales
WHERE product = 'Laptop'
GROUP BY MONTH(sale_date)
ORDER BY month;
Question 18
Hard
Show top salesperson per region (tricky — might need subquery).
Join with subquery that finds region-max totals.
-- One approach: correlated subquery
SELECT region, salesperson, total FROM (
  SELECT region, salesperson, SUM(amount) AS total FROM sales
  GROUP BY region, salesperson
) t
WHERE (region, total) IN (
  SELECT region, MAX(total) FROM (
    SELECT region, salesperson, SUM(amount) AS total FROM sales
    GROUP BY region, salesperson
  ) t2
  GROUP BY region
);

-- Cleaner: use window functions (chapter 18)
Question 19
Hard
What does GROUP BY region WITH ROLLUP add to the output?
Extra row.
An extra row with region=NULL representing the grand total
Question 20
Hard
Show each region's share of total revenue as a percentage.
Use a subquery for total.
SELECT
  region,
  SUM(amount) AS region_total,
  ROUND(SUM(amount) * 100.0 / (SELECT SUM(amount) FROM sales), 2) AS pct_of_total
FROM sales
GROUP BY region;

Multiple Choice Questions

MCQ 1
Which clause groups rows sharing a column value?
  • A. WHERE
  • B. GROUP BY
  • C. ORDER BY
  • D. HAVING
Answer: B
B is correct. GROUP BY groups rows. HAVING filters groups. WHERE filters rows.
MCQ 2
Which filters groups after aggregation?
  • A. WHERE
  • B. GROUP BY
  • C. HAVING
  • D. LIMIT
Answer: C
C is correct. HAVING runs after GROUP BY and can use aggregates. WHERE runs before GROUP BY and cannot.
MCQ 3
Where do non-aggregated SELECT columns go?
  • A. WHERE
  • B. GROUP BY
  • C. ORDER BY
  • D. HAVING
Answer: B
B is correct. Every non-aggregated column in SELECT must be in GROUP BY. Otherwise SQL doesn't know which value to show per group.
MCQ 4
Can WHERE reference an aggregate like SUM()?
  • A. Yes, always
  • B. No — WHERE runs before aggregation
  • C. Only with special keyword
  • D. Only on MySQL
Answer: B
B is correct. WHERE is evaluated before GROUP BY and aggregation. Use HAVING for aggregate filters.
MCQ 5
What is the correct syntactic clause order?
  • A. SELECT, FROM, GROUP BY, WHERE, HAVING
  • B. SELECT, FROM, WHERE, GROUP BY, HAVING
  • C. FROM, SELECT, WHERE, HAVING, GROUP BY
  • D. GROUP BY, FROM, WHERE, SELECT, HAVING
Answer: B
B is correct. Syntax order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. Cannot rearrange.
MCQ 6
What does SELECT region, SUM(amount) FROM sales GROUP BY region HAVING COUNT(*) > 3 return?
  • A. All regions regardless
  • B. Regions with more than 3 rows
  • C. Regions with SUM(amount) > 3
  • D. Regions with more than 3 unique products
Answer: B
B is correct. HAVING COUNT(*) > 3 keeps regions with more than 3 sales rows. Our data: North 5, South 4 pass; East 3, West 3 fail.
MCQ 7
Which query is valid?
  • A. SELECT region, salesperson FROM sales GROUP BY region;
  • B. SELECT region FROM sales GROUP BY region;
  • C. SELECT region, SUM(amount) FROM sales WHERE SUM(amount)>100 GROUP BY region;
  • D. SELECT region FROM sales HAVING region='North';
Answer: B
B is correct. Option A: salesperson is not aggregated or grouped — error in strict mode. Option C: WHERE cannot use aggregate. Option D: HAVING without GROUP BY (valid but misused — should be WHERE).
MCQ 8
In the logical execution order, when does SELECT run?
  • A. Before WHERE
  • B. Before GROUP BY
  • C. After HAVING, before ORDER BY
  • D. Last, after LIMIT
Answer: C
C is correct. Order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.
MCQ 9
You want total sales per region, showing only regions with total > 100000. Which is correct?
  • A. SELECT region, SUM(amount) FROM sales WHERE SUM(amount)>100000 GROUP BY region;
  • B. SELECT region, SUM(amount) FROM sales GROUP BY region WHERE SUM(amount)>100000;
  • C. SELECT region, SUM(amount) FROM sales GROUP BY region HAVING SUM(amount)>100000;
  • D. SELECT region, SUM(amount) FROM sales HAVING SUM(amount)>100000 GROUP BY region;
Answer: C
C is correct. HAVING filters groups after aggregation. Other options have wrong clause order or use WHERE with aggregate.
MCQ 10
GROUP BY region, product creates how many groups?
  • A. Number of regions
  • B. Number of products
  • C. Number of unique (region, product) combinations
  • D. Product of both counts
Answer: C
C is correct. Multi-column GROUP BY uses unique combinations. In our data: 11 such combinations.
MCQ 11
Can you use a column alias in HAVING in MySQL?
  • A. No, never
  • B. Yes, MySQL allows it
  • C. Only with DISTINCT
  • D. Only in PostgreSQL
Answer: B
B is correct. MySQL permits aliases in HAVING (because SELECT runs before HAVING in MySQL's evaluation). PostgreSQL requires the full expression. Either way, the expression form works in both.
MCQ 12
What does WITH ROLLUP add?
  • A. Cumulative running total
  • B. Extra row(s) with NULL in grouping column(s) for subtotals/grand total
  • C. Pagination
  • D. Sorting
Answer: B
B is correct. ROLLUP adds subtotal rows. For a single GROUP BY column, you get one extra row: NULL in that column with the grand total value.
MCQ 13
What is the output of SELECT COUNT(*) FROM sales GROUP BY region?
  • A. Single number = 15
  • B. One number per region (4 rows)
  • C. Number of unique regions
  • D. Error
Answer: B
B is correct. GROUP BY returns one row per group. COUNT(*) in each group counts rows in that group. 4 regions = 4 rows.
MCQ 14
Why can't plain GROUP BY solve 'top 3 salespeople per region'?
  • A. GROUP BY doesn't work with multiple columns
  • B. GROUP BY collapses each group to one row; LIMIT applies globally not per group
  • C. HAVING can't handle ranking
  • D. It's a MySQL limitation only
Answer: B
B is correct. GROUP BY gives one row per group. LIMIT trims the whole result, not per group. Top N per group requires window functions (ROW_NUMBER, RANK with PARTITION BY).
MCQ 15
Can WHERE reference a SELECT alias?
  • A. Yes, always
  • B. No, WHERE runs before SELECT computes aliases
  • C. Only in MySQL
  • D. Only with an index
Answer: B
B is correct. Aliases are defined in SELECT, which runs after WHERE. ORDER BY and HAVING (in MySQL) can reference them because they run after SELECT.
MCQ 16
What does HAVING region = 'North' do?
  • A. Syntax error — HAVING needs aggregate
  • B. Works (filters groups where region = 'North'), but WHERE is better
  • C. Only valid with GROUP BY
  • D. Returns all rows
Answer: B
B is correct. HAVING can reference grouped columns, not just aggregates. It works but is semantically wrong — use WHERE for row filtering. HAVING is slower because it runs after grouping.
MCQ 17
SELECT region, COUNT(*) FROM sales GROUP BY region HAVING COUNT(*)>100; what happens if no group matches?
  • A. NULL row
  • B. Zero rows returned
  • C. All rows returned
  • D. Error
Answer: B
B is correct. If HAVING filters out all groups, the result is empty. No special row is returned. This is different from a plain aggregate without GROUP BY which always returns exactly one row.
MCQ 18
Is SELECT SUM(amount) FROM sales GROUP BY region WITH ROLLUP ORDER BY region valid?
  • A. Yes
  • B. Invalid syntax
  • C. WITH ROLLUP cannot be used with ORDER BY
  • D. Need WHERE first
Answer: A
A is correct. WITH ROLLUP is valid with ORDER BY. The NULL (grand total) row will appear at the start or end depending on sort and NULL handling.
MCQ 19
SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 60000 ORDER BY AVG(salary) DESC; what does this return?
  • A. All employees earning > 60000
  • B. Departments where average salary exceeds 60000, sorted by that average descending
  • C. Error — can't use aggregate in ORDER BY
  • D. The single highest-paid department
Answer: B
B is correct. A fully valid GROUP BY query. Departments with avg > 60000 survive HAVING, then ORDER BY sorts by the same aggregate. ORDER BY can reference aggregates.
MCQ 20
How do you get unique count per group?
  • A. COUNT(*) per group
  • B. COUNT(DISTINCT column) per group
  • C. SUM(DISTINCT column)
  • D. GROUP BY DISTINCT column
Answer: B
B is correct. COUNT(DISTINCT product) per region tells you how many unique products each region sells. COUNT(*) would just count rows in each group.

Coding Challenges

Challenge 1: Sales By Region

Easy
Return total sales, count of sales, and average sale amount per region.
Sample Input
sales table.
Sample Output
North: 184000, 5, 36800 | South: 160000, 4, 40000 | East: 96000, 3, 32000 | West: 153000, 3, 51000
GROUP BY region.
SELECT region, SUM(amount) AS total, COUNT(*) AS sales_count, AVG(amount) AS avg_sale
FROM sales
GROUP BY region;

Challenge 2: Big Customers

Easy
List salespeople whose total revenue exceeds 80000.
Sample Input
sales table.
Sample Output
Aarav Sharma 96000, Priya Patel 102000, Karan Mehta 91000, Rohan Gupta 88000
HAVING SUM(amount) > 80000.
SELECT salesperson, SUM(amount) AS total FROM sales
GROUP BY salesperson
HAVING SUM(amount) > 80000
ORDER BY total DESC;

Challenge 3: Product Mix Per Region

Medium
For each region, count the number of unique products sold and the total revenue.
Sample Input
sales table.
Sample Output
North 3 products 184000 | South 3 products 160000 | East 3 products 96000 | West 2 products 153000
COUNT(DISTINCT product) and SUM.
SELECT region, COUNT(DISTINCT product) AS unique_products, SUM(amount) AS total
FROM sales
GROUP BY region
ORDER BY total DESC;

Challenge 4: Laptop Hotspots

Medium
Find regions where total Laptop revenue exceeds 100000. Show region and Laptop revenue.
Sample Input
sales table.
Sample Output
North 115000, South 115000, West 127000
WHERE + GROUP BY + HAVING.
SELECT region, SUM(amount) AS laptop_revenue
FROM sales
WHERE product = 'Laptop'
GROUP BY region
HAVING SUM(amount) > 100000
ORDER BY laptop_revenue DESC;

Challenge 5: Monthly Performance

Medium
Show monthly total revenue and number of sales for 2026, ordered by month.
Sample Input
sales table.
Sample Output
Jan: 3, 140000 | Feb: 4, 118000 | Mar: 4, 136000 | Apr: 4, 199000
GROUP BY MONTH(sale_date).
SELECT MONTH(sale_date) AS month, COUNT(*) AS num_sales, SUM(amount) AS revenue
FROM sales
WHERE YEAR(sale_date) = 2026
GROUP BY MONTH(sale_date)
ORDER BY month;

Challenge 6: Multi-Salesperson Products

Medium
List products sold by at least 3 different salespeople.
Sample Input
sales table.
Sample Output
Laptop (7 sellers), Phone (5 sellers), Tablet (3 sellers)
HAVING COUNT(DISTINCT salesperson) >= 3.
SELECT product, COUNT(DISTINCT salesperson) AS num_sellers
FROM sales
GROUP BY product
HAVING COUNT(DISTINCT salesperson) >= 3;

Challenge 7: Regional Share

Hard
Show each region, its total revenue, and its percentage share of grand total revenue (rounded to 2 decimals).
Sample Input
sales table. Grand total = 593000.
Sample Output
North 184000 31.03% | South 160000 26.98% | East 96000 16.19% | West 153000 25.80%
Use subquery for total.
SELECT
  region,
  SUM(amount) AS regional_total,
  ROUND(SUM(amount) * 100.0 / (SELECT SUM(amount) FROM sales), 2) AS pct_share
FROM sales
GROUP BY region
ORDER BY regional_total DESC;

Challenge 8: Most Popular Product Per Region

Hard
Find the best-selling product (by revenue) for each region. Return region, product, total revenue.
Sample Input
sales table.
Sample Output
North Laptop 115000, South Laptop 115000, East Laptop 54000, West Laptop 127000
Cannot use simple GROUP BY alone — need subquery or window functions.
-- Subquery approach (GROUP BY + subquery for max)
SELECT region, product, total FROM (
  SELECT region, product, SUM(amount) AS total
  FROM sales
  GROUP BY region, product
) t
WHERE (region, total) IN (
  SELECT region, MAX(total) FROM (
    SELECT region, product, SUM(amount) AS total
    FROM sales
    GROUP BY region, product
  ) t2
  GROUP BY region
);

-- Window function approach (cleaner; chapter 18)
SELECT region, product, total FROM (
  SELECT region, product,
         SUM(amount) AS total,
         ROW_NUMBER() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) AS rn
  FROM sales
  GROUP BY region, product
) t
WHERE rn = 1;

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