SQL GROUP BY: Choose the Grain of a Multi-Column Summary

The practice task

Summarize a fictional room-use log by region and shift. North has both morning and evening records; combining the region alone would hide that distinction.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE room_use(region TEXT, shift TEXT, seats INTEGER);
INSERT INTO room_use VALUES ('North','Morning',2),('North','Evening',3),('North','Morning',1),('South','Morning',4);

SELECT region, shift, COUNT(*) AS entries, SUM(seats) AS seats
FROM room_use GROUP BY region, shift
ORDER BY region, shift;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

The two North-Morning entries form one group with three seats. North-Evening forms a separate group with one entry and three seats. South-Morning remains another group. Equal totals do not make those distinct groups interchangeable.

Grouping only by region would produce two regional totals and remove the shift detail. Grouping by an additional unique log ID would instead preserve each source entry as its own group, defeating the intended shift-level summary.

Write the expected result grain in plain language: one row per region and shift. Then check that every selected descriptive column is part of that grain or follows a valid aggregation rule. An arbitrary label beside an aggregate can make a summary misleading even when its total is numerically correct.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Name the intended one-row-per relationship before writing the query.
• Check whether an added grouping column changes the grain.
• Use ORDER BY separately when the display order matters.