Interactively build and visualize how SQL aggregates data by grouping rows based on specific columns.
SELECT ... FROM SalesData;
GROUP BY divides filtered rows into groups with the same value for each grouping expression. An aggregate such as COUNT or SUM then summarizes each group. If you group orders by customer_id, the result has one row per customer who has at least one qualifying order.
The query first removes unpaid rows, then groups the remaining rows and calculates values. WHERE filters input rows; HAVING filters completed groups. See HAVING for a threshold example and aggregate functions for NULL behavior.
In standard SQL, every selected expression that is not aggregated must be part of the grouping key (or otherwise be functionally determined where the engine supports that rule). Selecting an arbitrary name next to a grouped customer_id can be invalid or ambiguous. SQLite permits some bare columns and may choose a value from an input row; do not rely on that behavior for portable results.
If a customer's name is needed, group by both columns, join the grouped result back to customers, or use a window function if rows should remain separate. Decide based on the output grain you want.
Rows with NULL in a grouping key are collected into one NULL group in common SQL engines. A group appears only when at least one input row belongs to it. A query with GROUP BY over no qualifying rows therefore returns no groups, while an aggregate without GROUP BY still returns one summary row.
If the output should be one row per month, group by the month expression and count customer IDs. Decide whether a customer with multiple orders in one month counts once; if so, COUNT(DISTINCT customer_id) expresses that rule. Be careful when grouping on formatted date text: sorting and time-zone conversion can change which month an event belongs to. Store or convert timestamps consistently, then group using an expression appropriate for the database. State the grain in a comment when the report is reused.
WHERE, grouping, and HAVING.About to run: switch Aggregate Function to AVG() - Average Value. Before it does — what happens to the readout?
Committing to an answer first is the point — the reveal runs the experiment on the visualisation above and reads the real value back, so nothing here is scripted.
Say the answer out loud before you reveal it — recalling it is what makes it stick, and rereading it is not.
What does this module say about “What a group represents”?
What does this module say about “Select only values that describe the group”?
What does this module say about “NULL and empty groups”?
Group rows by a column and watch COUNT, SUM and AVG collapse many rows into one row per group, live against a sample table.