Subsection 6.2

GROUP BY

~4 min

Reading

GROUP BY groups rows that share the same value in one or more columns.

Try this analogy

Think of GROUP BY as sorting laundry

You're sorting a load of laundry. Right now it's one pile of mixed clothes. You separate by color: a pile of whites, a pile of darks, a pile of colors. Then you count each pile: "3 whites, 7 darks, 2 colors."

That's GROUP BY. The GROUP BY column is the rule you're sorting on (color). The aggregate function : COUNT, SUM, AVG : is what you do to each pile.

Organizing version: GROUP BY zip_code piles your signups by zip code. COUNT(*) tells you how big each pile is. The result is one row per zip code, showing how many signups came from each : exactly the report a canvass director needs.

It is commonly used with aggregate functions like COUNT, SUM, AVG, MIN, and MAX.

Basic pattern:

SELECT column1, COUNT(*) AS row_count
FROM `project.dataset.table`
WHERE condition
GROUP BY column1
ORDER BY row_count DESC;

The order of clauses matters:

SELECT column1, function_name(column2)
FROM table_name
WHERE condition
GROUP BY column1, column2
ORDER BY column1, column2;

A few rules:

GROUP BY is used with SELECT.
GROUP BY comes after WHERE.
ORDER BY usually comes after GROUP BY.
Columns in the SELECT list that are not aggregated usually need to appear in the GROUP BY.

For organizing work, GROUP BY helps answer questions like:

Which zip codes had the most signups?
Which counties have the most missing phone numbers?
How many RSVPs came from each source?
Which canvass turfs produced the most contacts?
Which outreach channel reached the most people this month?

Learner action

Write a grouped query using your table. Start with this pattern:

SELECT [grouping_column], COUNT(*) AS row_count
FROM `[project].[dataset].[table]`
GROUP BY [grouping_column]
ORDER BY row_count DESC
LIMIT 20;

GROUP BY column says "give me one row per unique value in that column." Every non-aggregate column in your SELECT has to appear in GROUP BY : or BigQuery throws a syntax error.

Clause order (from the deck, slide 47)

SELECT column1, function_name(column2)
FROM `project.dataset.table`
WHERE condition
GROUP BY column1
ORDER BY column1;

The clauses always run in this order: SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT.

Worked example : voter count by state

SELECT state, COUNT(*) AS voter_count
FROM `prj.dataset.voters`
GROUP BY state;

Worked example : top states by total births in 2023 (public dataset)

SELECT state, SUM(number) AS births
FROM `bigquery-public-data.usa_names.usa_1910_current`
WHERE year = 2023
GROUP BY state
ORDER BY births DESC
LIMIT 10;

Diagram 6.2 · GROUP BY collapses rows. 30,000 detailed rows become 51 category rows : one per state : with the total within each group.

GROUP BY is how you go from “one count” to “one count per category.” The shape: SELECT category, COUNT(*) FROM table GROUP BY category. Try it.

Action: Pick a column on your table that makes a useful category (zip code, source, status, year). Write the GROUP BY version of your query. Run it.

Slide to complete