All sample questions / SQL

What does SELECT event, SUM(revenue) FROM ticket_sales GROUP BY event ORDER BY ... return? SQL Grouping and Aggregation

Block 3, Grouping and Aggregation, question 15 of the SQL sample

CREATE TABLE ticket_sales (id INTEGER, event TEXT, revenue INTEGER);
INSERT INTO ticket_sales VALUES
  (1, 'jazz', 1200),
  (2, 'jazz', NULL),
  (3, 'rock', NULL),
  (4, 'rock', NULL),
  (5, 'pop', 800);

SELECT event, SUM(revenue) FROM ticket_sales GROUP BY event ORDER BY event

What does this query return?

  1. A. pop | 800 jazz | 1200 rock | NULL
  2. B. jazz | 1200 pop | 800 rock | 0
  3. C. jazz | 1200 pop | 800 rock | NULL
  4. D. jazz | NULL pop | 800 rock | NULL
Show the answer and why each option is right or wrong

Answer, C. jazz | 1200 pop | 800 rock | NULL

A. pop | 800 jazz | 1200 rock | NULL
The totals are correct but the events are not sorted alphabetically, jazz comes before pop before rock.
B. jazz | 1200 pop | 800 rock | 0
This treats a group with only NULL revenue as summing to zero, but SUM returns NULL, not zero, when there is nothing non-NULL to add.
C. jazz | 1200 pop | 800 rock | NULL
Correct. Jazz totals 1200, pop totals 800, and rock, whose two rows are both NULL, returns NULL.
D. jazz | NULL pop | 800 rock | NULL
This treats the NULL row in jazz as poisoning its whole SUM, but SUM skips that row and keeps the 1200 from the other one.

A NULL value contributes nothing to a SUM, it is simply skipped, and a group made entirely of NULLs has nothing left after skipping, which is why SUM reports NULL rather than zero in that case.

The answer above is what SQLite and DuckDB both returned when the snippet was run, not what someone expected it to do.

This question comes from a bank of 502 SQL interview questions, all built the same way, sold as one PDF with the full answer key.

Get the 502 SQL questions, 9 euros

Or SQL, Python, pandas and NumPy together, 1,636 questions, 19 euros

Or play the free SQL sample, twenty questions in the browser, also as a free PDF.

Previous question Next question

Did this save you a lookup? Buy me a coffee on Ko-fi.