Aggregation & summary — quiz

COUNT(*) is 500 and COUNT(email) is 480. Why?

1. Duplicate rows
2. 20 rows have a null email
3. A join issue
4. An index issue

Answer: 20 rows have a null email — COUNT on a column ignores nulls.

Why does WHERE SUM(total) > 1000 fail?

1. SUM needs GROUP BY
2. WHERE is evaluated before aggregation
3. SUM is not a function
4. It needs parentheses

Answer: WHERE is evaluated before aggregation — The aggregate does not exist at the point WHERE is applied.

Which query is valid?

1. SELECT region, city, SUM(t) FROM o GROUP BY region
2. SELECT region, SUM(t) FROM o GROUP BY region
3. SELECT region, city FROM o GROUP BY SUM(t)
4. SELECT SUM(region) FROM o GROUP BY t

Answer: SELECT region, SUM(t) FROM o GROUP BY region — Every non-aggregated column must appear in GROUP BY.

Adding a column to GROUP BY generally:

1. Reduces the row count
2. Increases the row count
3. Has no effect
4. Causes an error

Answer: Increases the row count — Finer grouping produces more, smaller groups.

You need only regions with revenue above 100k. Use:

1. WHERE
2. HAVING
3. ORDER BY
4. DISTINCT

Answer: HAVING — The condition is on an aggregate, which exists only after grouping.

Putting a row-level filter in HAVING instead of WHERE:

1. Errors
2. Usually works but is slower
3. Is always faster
4. Changes the result

Answer: Usually works but is slower — More rows reach the grouping step and are then thrown away.

Why is ELSE omitted in COUNT(CASE WHEN x THEN 1 END)?

1. It is optional syntax
2. The resulting null makes COUNT skip non-matching rows
3. ELSE is invalid there
4. To improve performance

Answer: The resulting null makes COUNT skip non-matching rows — COUNT ignores nulls, so the missing ELSE is what makes the count selective.

Conditional aggregation replaces:

1. Indexes
2. Multiple separate queries or a self-join
3. GROUP BY
4. Window functions

Answer: Multiple separate queries or a self-join — It computes several conditional measures in one pass.