Subsection 6.1

From rows to counts

~4 min

Reading

So far, you have inspected rows, selected columns, filtered records, and sorted results.

Now you will summarize.

Summarizing turns many rows into a smaller finding. Instead of reading every signup, you might count signups by zip code. Instead of scanning every RSVP, you might count RSVPs by source. Instead of reviewing every contact, you might count missing phone numbers by county.

The slide deck introduces aggregate functions:

COUNT(): counts the number of rows.
SUM(): adds values.
AVG(): computes an average.
MIN(): returns the minimum value.
MAX(): returns the maximum value.

For this course, COUNT() is the main function.

Example:

SELECT source, COUNT(*) AS signup_count
FROM `team.signups`
GROUP BY source
ORDER BY signup_count DESC;

This query counts rows by source. The result might tell an organizer whether surveys, events, doors, or another channel produced the most signups.

A summary query should still connect to an organizing decision. Do not summarize just because you can. Summarize because the team needs a clearer next step.

Learner action

Name the summary your team needs. Use this sentence:

I need to count records by [field] so we can decide [organizing action].

From the deck (slide 31 + slide 46). The five aggregate functions that cover 95% of organizing use cases:

Function	What it does	Example
`COUNT(*)`	Counts rows.	`SELECT COUNT(*) FROM voters WHERE age >= 18;`
`COUNT(DISTINCT col)`	Counts unique values.	`COUNT(DISTINCT state)`
`SUM(col)`	Adds values.	`SUM(donation_amount)`
`AVG(col)`	Average of values.	`AVG(donation_amount)`
`MIN(col)` / `MAX(col)`	Smallest / largest value.	`MIN(age), MAX(age)`

Worked example : totals and uniques in one query

SELECT COUNT(*) AS total_rows,
       COUNT(DISTINCT state) AS unique_states
FROM `bigquery-public-data.usa_names.usa_1910_current`
WHERE year = 2023;

The NULL trap, part 2

Aggregate functions silently skip NULLs. If 40% of your donation_amount rows are null, AVG(donation_amount) is the average of the other 60%. That can mislead. When a column might be heavily null, sanity-check with COUNT(col) / COUNT(*) to see how populated it actually is.

COUNT(*) collapses rows into a single number. It's how you go from “here's a list of voters” to “here are how many.”

Action: Add a COUNT(*) to your filtered query from Module 5. Note the total. That's the universe your summary will draw from.

Slide to complete