SQL Quiz: Aggregates With Where
10 questions · exam conditions
0:00
Aggregates With WhereQuestion 1 of 10

Consider this query: SELECT category, COUNT(*) AS order_count, AVG(amount) AS avg_amount FROM orders WHERE amount >= 100 GROUP BY category; Some categories contain orders below 100, and one category has no orders at or above 100.

Which interpretation of the query is correct?

Both aggregates use only orders of at least 100100, and a category with no such orders is absent
The count uses only orders of at least 100100, but the average uses every order in each category
The count uses every order in each category, but the average uses only orders of at least 100100
Both aggregates use only orders of at least 100100, and an empty category appears with zero values
← Back to quizzes

SQL Quiz

SQL Quiz: Aggregates With Where

Practice Aggregates With Where in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Aggregates With Where, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

Consider this query: SELECT category, COUNT(*) AS order_count, AVG(amount) AS avg_amount FROM orders WHERE amount >= 100 GROUP BY category; Some categories contain orders below 100, and one category has no orders at or above 100.

Which interpretation of the query is correct?

  1. Both aggregates use only orders of at least 100100, and a category with no such orders is absent (correct answer)
  2. The count uses only orders of at least 100100, but the average uses every order in each category
  3. The count uses every order in each category, but the average uses only orders of at least 100100
  4. Both aggregates use only orders of at least 100100, and an empty category appears with zero values
Explanation: Whenever you see a query combining WHERE and GROUP BY with aggregate functions, remember this key principle: the WHERE clause filters rows before any grouping or aggregation occurs. This means every aggregate function — COUNT, AVG, SUM, and others — operates only on the rows that survived the filter. In this query, WHERE amount >= 100 eliminates all orders below 100100 before GROUP BY category ever runs. So both COUNT(*) and AVG(amount) see only the filtered dataset. A category whose orders are all below 100100 has zero rows remaining after the filter, meaning it produces no group at all and disappears from the results entirely. That makes A correct — both aggregates use only qualifying orders, and the category with no orders at or above 100100 simply does not appear. B is wrong because it implies AVG somehow bypasses the WHERE filter and reaches back to all orders. That's not how SQL works — no aggregate can "see" rows already excluded by WHERE. C makes the same mistake in reverse, claiming COUNT(*) tallies all orders while AVG stays filtered. Again, both aggregates share the same filtered row set. D is tempting but incorrect: it assumes the missing category appears with zero values. In reality, GROUP BY only creates groups from rows that exist after filtering — if no rows belong to a category, no group is created and no row appears in the output. To show zero values for empty groups, you would need a LEFT JOIN or similar construct. A useful rule of thumb: WHERE filters before aggregation; HAVING filters after. Keeping that distinction clear will help you reason through any query combining these clauses.

Question 2

Tables customers and orders are related by customer_id. A report must list every customer and count that customer's shipped orders placed during 2026. Customers with no qualifying orders must appear with a count of zero.

Which query meets the requirement without removing customers who have no qualifying orders?

  1. SELECT c.customer_id, COUNT(o.order_id) FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.status = 'shipped' AND o.order_date >= '2026-01-01' AND o.order_date < '2027-01-01' GROUP BY c.customer_id;
  2. SELECT c.customer_id, COUNT(o.order_id) FROM customers c JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'shipped' AND o.order_date >= '2026-01-01' AND o.order_date < '2027-01-01' GROUP BY c.customer_id;
  3. SELECT c.customer_id, COUNT(*) FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.order_date >= '2026-01-01' AND o.order_date < '2027-01-01' GROUP BY c.customer_id;
  4. SELECT c.customer_id, COUNT(o.order_id) FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'shipped' AND o.order_date >= '2026-01-01' AND o.order_date < '2027-01-01' GROUP BY c.customer_id; (correct answer)
Explanation: When combining a LEFT JOIN with filtering conditions, the placement of those conditions — in the ON clause versus the WHERE clause — completely changes which rows survive. This is one of the trickiest patterns in SQL. A LEFT JOIN preserves every row from the left table (customers), filling right-side columns with NULL when no match exists. But if you then filter on right-side columns in the WHERE clause, you silently discard those NULL rows, effectively converting the LEFT JOIN into an INNER JOIN. That's exactly the trap in option A: moving status = 'shipped' and the date range into WHERE eliminates customers with no qualifying orders, producing an incomplete report. Option D is correct because all three filtering conditions (status, order_date range) live in the ON clause. This means the join attempts to match only shipped 2026 orders, but customers without any such match still appear — their order columns just come back as NULL. COUNT(o.order_id) counts non-NULL values, so those customers correctly receive a count of zero. Option B uses an INNER JOIN, so customers with no qualifying orders are dropped entirely — it can never produce zero-count rows. Option C moves the status filter into WHERE... wait, actually C omits the status filter entirely, meaning it counts all 2026 orders regardless of status, producing inflated counts that don't match the requirement. The key strategy: whenever you need "all left-side rows plus optional matching," use a LEFT JOIN and place every filter on the optional (right) table inside the ON clause, never in WHERE.

Question 3

A purchases table contains five rows: two completed purchases for customer 10, one completed purchase with a NULL customer ID, one pending purchase for customer 11, and one completed purchase for customer 12.

What does SELECT COUNT(DISTINCT customer_id) FROM purchases WHERE status = 'completed'; return?

  1. It returns 44 because four completed purchase rows remain after filtering
  2. It returns 33 because the NULL customer ID forms a distinct value
  3. It returns 22 because the distinct non-NULL IDs are 10 and 12 (correct answer)
  4. It returns 11 because only one customer has multiple completed purchases
Explanation: When combining COUNT(DISTINCT ...) with a WHERE filter, you need to apply the operations in the right mental order: first the filter removes rows, then DISTINCT collapses duplicates, then COUNT tallies what remains — and critically, NULL values are never counted. Starting with the filter WHERE status = 'completed', three rows survive: two purchases for customer 10, one for customer 12, and one with a NULL customer ID. Now DISTINCT customer_id across those rows gives you: 10, 12, and NULL. Here's the key rule — COUNT() ignores NULLs entirely, even when DISTINCT is applied. So only customer IDs 10 and 12 are counted, returning 22. That makes C correct. A is wrong because it confuses COUNT(*) with COUNT(DISTINCT customer_id). Yes, four rows have status = 'completed', but you're not counting rows — you're counting distinct non-NULL customer ID values. B falls into the common trap of treating NULL as a countable distinct value. It isn't — COUNT skips NULL regardless of DISTINCT. D misreads the question entirely; having two rows for customer 10 doesn't reduce the distinct count to 1. Both 10 and 12 are distinct, non-NULL IDs that get counted. A good rule of thumb: whenever you see COUNT(column_name), remember it never counts NULLs — only COUNT(*) counts every row unconditionally. Pair that with DISTINCT, and you're counting unique, non-NULL values only.

Question 4

An expenses table has columns department, expense_date, and amount. A report must show departments whose total expenses on or after January 1, 2026 exceed 10000.

Which query applies both conditions at the appropriate stages of aggregation?

  1. SELECT department, SUM(amount) FROM expenses WHERE expense_date >= '2026-01-01' AND SUM(amount) > 10000 GROUP BY department;
  2. SELECT department, SUM(amount) FROM expenses GROUP BY department HAVING expense_date >= '2026-01-01' AND SUM(amount) > 10000;
  3. SELECT department, SUM(amount) FROM expenses WHERE expense_date >= '2026-01-01' GROUP BY department HAVING SUM(amount) > 10000; (correct answer)
  4. SELECT department, SUM(amount) FROM expenses WHERE amount > 10000 GROUP BY department HAVING MIN(expense_date) >= '2026-01-01';
Explanation: When filtering rows in SQL, you need to ask yourself when that filter applies — before or after grouping? This question tests whether you can correctly assign row-level conditions to WHERE and aggregate-level conditions to HAVING. WHERE runs before rows are grouped, so it filters individual rows based on column values. HAVING runs after grouping, so it filters entire groups based on aggregate results like SUM(). Applying these filters at the wrong stage either causes a syntax error or produces logically incorrect results. Option C is correct because it handles each condition at the right stage. WHERE expense_date >= '2026-01-01' first eliminates any rows outside the target date range, ensuring only qualifying expenses are fed into the aggregation. Then HAVING SUM(amount) > 10000 checks whether each department's total — computed from those filtered rows — clears the threshold. This is exactly the two-stage logic the question describes. Option A fails because SUM(amount) > 10000 appears inside the WHERE clause. Aggregate functions are not allowed in WHERE — SQL hasn't grouped anything yet, so there's nothing to sum. This causes a syntax error. Option B does the opposite mistake: expense_date >= '2026-01-01' is placed in HAVING, but expense_date is a row-level column, not an aggregate. SQL would need to evaluate it per group with no clear meaning, producing unreliable or erroneous results. Option D changes the logic entirely — it filters on amount > 10000 per row rather than the total, and uses MIN(expense_date) to approximate the date condition, which doesn't match the requirement. Your takeaway: WHERE filters rows, HAVING filters groups. If a condition involves an aggregate function, it belongs in HAVING; if it references a plain column, it belongs in WHERE.

Question 5

An orders table has four rows: a paid order with amount 40, a pending order with amount 30, a cancelled order with amount 20, and an order whose status is NULL with amount 50.

What is returned by SELECT SUM(amount) FROM orders WHERE status <> 'cancelled';?

  1. 7070, because only the paid and pending rows satisfy the comparison (correct answer)
  2. 120120, because every row except the cancelled row is included
  3. 9090, because the cancelled row remains but the NULL row is excluded
  4. 5050, because only the row with an unknown status remains eligible
Explanation: Whenever SQL evaluates a WHERE clause, it applies three-valued logic: a condition can be TRUE, FALSE, or UNKNOWN. This is the critical concept being tested here. When you compare any value against NULL using standard operators like <>, the result is always UNKNOWN — not TRUE — so the row is excluded from the result set. Working through the data: the paid row (4040) passes status <> 'cancelled', the pending row (3030) passes, the cancelled row (2020) fails, and the NULL-status row (5050) produces UNKNOWN and is silently dropped. Only the paid and pending rows survive, giving 40+30=7040 + 30 = 70. That makes A correct. B assumes NULL <> 'cancelled' evaluates to TRUE, which would include the 5050 row and yield 120120. This is the most common trap — students intuitively think "NULL is not the word 'cancelled,' so it should pass." But SQL never treats UNKNOWN as TRUE. C gets the logic exactly backwards, keeping the cancelled row and dropping the NULL row to reach 9090. This reflects confusion about which comparison returns FALSE versus UNKNOWN. D suggests only the NULL row survives, which misunderstands the filter entirely — the NULL row is the one excluded, not the one that passes. Study tip: Anytime you see a WHERE clause with <>, =, <, or >, ask yourself whether any column values could be NULL. If so, those rows will be silently excluded. To explicitly include or check for NULL, you must use IS NULL or IS NOT NULL.

Question 6

An events table stores event_time as a timestamp. A report run over multiple years must return the total value for events occurring in January 2026, including every instant on January 31 but excluding every instant on February 1.

Which WHERE clause most reliably defines the rows included in the aggregate?

  1. WHERE event_time BETWEEN '2026-01-01 00:00:00' AND '2026-02-01 00:00:00'
  2. WHERE event_time >= '2026-01-01 00:00:00' AND event_time < '2026-02-01 00:00:00' (correct answer)
  3. WHERE EXTRACT(MONTH FROM event_time) = 1
  4. WHERE event_time > '2026-01-01 00:00:00' AND event_time <= '2026-01-31 00:00:00'
Explanation: When filtering timestamps in SQL, the boundary conditions matter enormously — a single misplaced second can silently include or exclude data you don't intend. The goal here is to capture every moment from the very start of January 1, 2026 up to (but not including) February 1, 2026. Option B achieves this precisely: >= '2026-01-01 00:00:00' includes the first instant of January, and < '2026-02-01 00:00:00' excludes every instant on February 1 — even 00:00:00.000001. This half-open interval pattern is the gold standard for date range filtering on timestamps. Option A uses BETWEEN, which in SQL is inclusive on both ends. That means the timestamp 2026-02-01 00:00:00 exactly is included, pulling in the very first instant of February — a subtle but real data error. Option C uses EXTRACT(MONTH FROM event_time) = 1, which returns January across all years in the table. Since the report spans multiple years, this would aggregate January 2024, January 2025, and so on — completely breaking the year-specific requirement. It also prevents the database from using an index on event_time, hurting performance. Option D sets the upper bound to 2026-01-31 00:00:00, which excludes everything after midnight on January 31 — meaning 23 hours and 59 minutes of that final day are silently dropped. As a study habit, always prefer the half-open interval pattern (>= lower bound, < exclusive upper bound) when working with timestamps. It handles sub-second precision cleanly and avoids the off-by-one traps that BETWEEN and hard-coded end dates create.

Question 7

An employees table has three active employees whose salary values are 60000, NULL, and 90000. It also has one inactive employee with a salary of 70000.

What values are returned by SELECT COUNT(*), COUNT(salary) FROM employees WHERE active = TRUE;?

  1. COUNT(*) returns 44, while COUNT(salary) returns 33
  2. COUNT(*) returns 33, while COUNT(salary) returns 33
  3. COUNT(*) returns 33, while COUNT(salary) returns 22 (correct answer)
  4. COUNT(*) returns 22, while COUNT(salary) returns 22
Explanation: When working with SQL aggregate functions, you need to track two things separately: what the WHERE clause filters out, and how COUNT(*) differs from COUNT(column). The WHERE active = TRUE clause runs first, eliminating the inactive employee with a salary of 7000070000. That leaves exactly 33 rows to aggregate — the employees earning 6000060000, NULL, and 9000090000. Now here's the critical distinction: COUNT(*) counts every row that survives the filter, regardless of column values, giving you 33. However, COUNT(salary) only counts rows where salary is not NULL — so it skips the employee with a NULL salary and returns 22. That makes C the correct answer. A is wrong because it counts 44 rows for COUNT(*), which would only be true if the WHERE clause wasn't applied — all four employees would be included. The inactive employee is already excluded before any aggregation happens. B is wrong because it assumes COUNT(salary) behaves like COUNT(*) and includes the NULL row. This is the most common trap: forgetting that COUNT(column) silently ignores NULL values. D is wrong on both counts — it applies the NULL-ignoring behavior to COUNT(*) as well, which is never how it works. COUNT(*) always counts rows, never column values. A reliable memory trick: think of COUNT(*) as counting seats at the table and COUNT(column) as counting non-empty plates. On SQL exams, questions pairing these two functions almost always hinge on NULL handling.

Question 8

An attempts table stores status and score. A report must calculate the average score of completed attempts only. For this report, a completed attempt with a NULL score must contribute a score of zero. Non-completed attempts must not affect either the numerator or denominator.

Which query produces the required average?

  1. SELECT AVG(COALESCE(score, 0)) FROM attempts WHERE status = 'completed'; (correct answer)
  2. SELECT COALESCE(AVG(score), 0) FROM attempts WHERE status = 'completed';
  3. SELECT AVG(score) FROM attempts WHERE status = 'completed' AND score IS NOT NULL;
  4. SELECT AVG(CASE WHEN status = 'completed' THEN COALESCE(score, 0) ELSE 0 END) FROM attempts;
Explanation: When a question asks you to average a filtered subset while treating NULLs as a specific value, you need to think carefully about where the NULL replacement happens — before or after aggregation — because the order changes the result entirely. AVG works by summing non-NULL values and dividing by the count of non-NULL rows. So if you want NULL scores to count as zero in both the numerator and denominator, you must convert them to zero before AVG sees them. That's exactly what A does: COALESCE(score, 0) replaces each NULL with 0 first, so every completed row contributes to the count, and NULL scores add 0 to the sum. The WHERE clause cleanly excludes non-completed attempts from both numerator and denominator. B applies COALESCE after AVG, meaning NULLs are silently dropped from the average calculation. The outer COALESCE only kicks in if the entire result is NULL (i.e., no completed rows exist at all) — it does nothing to fix individual NULL scores being excluded. C explicitly filters out NULL scores with AND score IS NOT NULL, which shrinks the denominator. A completed attempt with no score is simply ignored rather than counted as zero — violating the requirement. D removes the WHERE filter and instead uses a CASE expression, but it assigns 0 to non-completed rows rather than excluding them. This pollutes the denominator with non-completed attempts, artificially lowering the average. A useful mental rule: if you want NULLs to count as a value, transform them before aggregation with COALESCE(column, value) inside the aggregate function, not outside it.

Question 9

An orders table contains these rows: East, paid, amount 80; East, pending, amount 120; West, paid, amount 50; West, refunded, amount 20; and North, pending, amount 200. The columns are region, status, and amount.

What result is produced by SELECT region, SUM(amount) FROM orders WHERE status = 'paid' GROUP BY region;?

  1. East with 200200, West with 7070, and North with 200200
  2. East with 8080 and West with 5050; North is absent (correct answer)
  3. East with 8080, West with 5050, and North with 00
  4. East with 200200 and West with 7070; North is absent
Explanation: When a SQL query combines WHERE and GROUP BY, you need to apply them in the correct mental order: filtering happens before grouping. Think of it as a two-step pipeline — WHERE trims the rows first, then GROUP BY organizes whatever remains. Here, WHERE status = 'paid' eliminates every row that isn't paid. Scanning the table, only two rows survive: East/paid/8080 and West/paid/5050. North's only row has status "pending," so it's filtered out entirely before grouping ever occurs. GROUP BY region then collapses those two remaining rows into their own groups, and SUM(amount) produces 8080 for East and 5050 for West — exactly what answer B describes. Answer A is wrong on two counts: it incorrectly includes North (which was filtered out) and inflates East's total to 200200 and West's to 7070, as if the WHERE clause were ignored and all statuses were summed. Answer C makes a similar mistake by keeping North in the result, but assigns it 00 — SQL doesn't return a group with no qualifying rows at all; it simply omits that region entirely. Answer D gets East and West's sums wrong (200200 and 7070) by summing amounts across all statuses, not just "paid," even though it correctly excludes North. A reliable study tip: whenever you see WHERE paired with GROUP BY, always ask yourself "what rows survive the filter?" before thinking about the aggregation. Rows that don't pass WHERE don't contribute to any group — they vanish completely, which is why North disappears here rather than showing 00.

Question 10

A tickets table has five rows: a high-priority closed ticket aged 2 days; a low-priority open ticket aged 10 days; a high-priority open ticket aged 3 days; a low-priority open ticket aged 4 days; and a low-priority closed ticket aged 20 days.

What count is returned by SELECT COUNT(*) FROM tickets WHERE priority = 'high' OR status = 'open' AND age_days > 7;?

  1. 00, because all three conditions must be true simultaneously for a ticket to qualify
  2. 11, because the age_days > 7 test applies to both the priority branch and the status branch
  3. 44, because the age_days > 7 test applies only to open tickets, meaning all high-priority tickets and all open tickets qualify
  4. 33, because AND is evaluated before OR, so only high-priority tickets and low-priority open tickets older than 7 days qualify (correct answer)
Explanation: When you see a query mixing AND and OR without parentheses, your first job is to mentally apply operator precedence: SQL always evaluates AND before OR, just like multiplication before addition in arithmetic. That means priority = 'high' OR status = 'open' AND age_days > 7 is parsed as priority = 'high' OR (status = 'open' AND age_days > 7). Now walk through all five rows: the high-priority closed ticket (age 2) qualifies via the OR left side — priority is high. The high-priority open ticket (age 3) also qualifies — priority is high. The low-priority open ticket aged 10 qualifies via the right side — it's open AND older than 7 days. The low-priority open ticket aged 4 does not qualify — it's open but fails age_days > 7. The low-priority closed ticket aged 20 does not qualify — neither condition is met. That gives you 3 rows, confirming D is correct. Choice A is wrong because it treats the query as if all three conditions are joined by AND, which would be far more restrictive. Choice B incorrectly applies the age_days > 7 filter to both branches equally, which would disqualify the two young high-priority tickets and return only 1 row. Choice C assumes OR is evaluated first, grouping all high-priority and all open tickets together and ignoring the age filter on the open side — that precedence reversal is the classic trap here. Your strategy: whenever you see AND and OR mixed in a WHERE clause, rewrite the implicit grouping with parentheses before counting rows. That one habit eliminates precedence mistakes every time.