Questions: SQL Aggregation and GROUP BY

Question 1

A database has an 'orders' table with columns customer_id, order_date, and amount. You want to show only customers who have placed more than 5 orders total. Where does this filter belong?

• A. In a WHERE clause: WHERE COUNT(order_id) > 5
• B. In a HAVING clause: HAVING COUNT(*) > 5, after GROUP BY customer_id
• C. In a WHERE clause: WHERE order_count > 5, since WHERE runs after aggregation
• D. Either WHERE or HAVING work — they filter at different stages but produce identical results

Answer: (B)

The count of orders per customer is an aggregate value — it doesn't exist until after GROUP BY groups the rows. HAVING is specifically designed to filter on aggregate results after grouping. WHERE cannot reference COUNT(*) because WHERE runs before grouping, when the per-customer count hasn't been computed yet. Option A would cause an error. Option C is wrong about when WHERE runs (it runs before aggregation). This WHERE/HAVING distinction is the central skill in aggregation.

Question 2

Consider: SELECT region, sales_rep, SUM(amount) FROM sales GROUP BY region. What is wrong with this query?

• A. SUM(amount) is wrong — you should use AVG(amount) when grouping by region
• B. Nothing — this is a valid query that groups by region and shows each sales rep's contribution
• C. sales_rep appears in SELECT but not in GROUP BY and is not aggregated — this is a SQL error
• D. You need a HAVING clause whenever you use GROUP BY

Answer: (C)

Every column in SELECT must either appear in GROUP BY or be wrapped in an aggregate function. When rows are grouped by region, multiple rows (with different sales_rep values) collapse into one output row per region. SQL cannot know which single sales_rep value to display alongside the regional SUM — so most SQL engines raise an error (or return an arbitrary/undefined value). The fix is to either add sales_rep to GROUP BY or remove it from SELECT.

Question 3

The HAVING clause is evaluated after grouping is complete, which is why it can reference aggregate functions like SUM() and COUNT().

• T. True
• F. False

Answer: True

Execution order matters here: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. HAVING operates on the grouped result set, after each group has been formed and its aggregates computed. This is why HAVING SUM(amount) > 10000 works — by the time HAVING is evaluated, SUM(amount) exists as a value per group. WHERE, evaluated earlier, operates on individual rows before any grouping occurs.

Question 4

COUNT(*) and COUNT(column_name) usually return the same result when applied to the same table.

• T. True
• F. False

Answer: False

COUNT(*) counts all rows in the group, including rows where any column has NULL values. COUNT(column_name) counts only rows where that specific column is NOT NULL. If any rows have a NULL in the counted column, COUNT(column_name) < COUNT(*). This distinction is important when dealing with optional fields — for example, COUNT(*) might count all customers while COUNT(phone_number) counts only those with a phone number on record.

Question 5

Explain why the distinction between WHERE and HAVING depends on *when* each clause is evaluated in query execution, and give an example of a filter that belongs in each.

Model answer: WHERE is evaluated before GROUP BY — it filters individual rows that enter the grouping process. HAVING is evaluated after GROUP BY — it filters the resulting groups based on their aggregate values. A WHERE filter applies to raw row data (e.g., WHERE order_date >= '2024-01-01' to include only 2024 orders before grouping). A HAVING filter applies to computed group statistics (e.g., HAVING SUM(amount) > 10000 to show only high-revenue groups). The diagnostic test: if the condition references an aggregate function, it must be HAVING; if it references a raw column, it can be WHERE.

The timing distinction explains why you cannot write WHERE SUM(amount) > 10000 — the SUM doesn't exist yet when WHERE is evaluated. Understanding execution order (FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY) resolves most aggregation query bugs. The practical consequence: using WHERE to pre-filter rows before grouping is also a performance optimization — it reduces the number of rows that need to be grouped.