Aggregating Techniques

📚 Schema reference · towns & counties 3 tables

fips

fips
name
state

pnw_counties

county_id
county
state
fips_short
county_seat
county_seat_town_id
year_established
origin
etymology
population_2022
land_area_sq_mi

pnw_towns

town_id
town
state
primary_county
secondary_county
tertiary_county
primary_county_id
secondary_county_id
tertiary_county_id
population_2020_census
population_2010_census
land_area_sq_mi

Numerical summaries

Aggregate functions (AVG, SUM, MIN, MAX, COUNT) collapse multiple rows into summary statistics.

An aggregate turns a whole column into one value

SELECT SUM(area), AVG(area),
       MIN(area), MAX(area)
  FROM counties;

Scroll to build the diagram, one step at a time.

Step 1 · five rows, one gap Five counties and their land areas. Wheeler has no area recorded, so that cell is NULL.

Step 2 · SUM SUM(area) adds the column into a single value, 100. Five rows went in and one row came out. The NULL is skipped, not treated as zero.

Step 3 · AVG skips NULL too AVG(area) is 100 divided by the 4 values that exist, which is 25. Counting Wheeler as a zero would have given 20. A column with many NULLs can make an average describe fewer rows than you think.

Step 4 · MIN and MAX MIN and MAX each pick one value that is already in the column (ringed in blue). They return the value only: the result has no county name, so you cannot tell from it that 10 belongs to Benton.

Step 5 · all in one SELECT Every aggregate reads the same rows, so they can share one SELECT and still return one row. COUNT(*) says 5 and COUNT(area) says 4, which is how you spot the gap. With no GROUP BY, the whole table is a single group.

Text version of this diagram

Step 1 · five rows, one gap. Five counties and their land areas. Wheeler has no area recorded, so that cell is NULL.
Step 2 · SUM. SUM(area) adds the column into a single value, 100. Five rows went in and one row came out. The NULL is skipped, not treated as zero.
Step 3 · AVG skips NULL too. AVG(area) is 100 divided by the 4 values that exist, which is 25. Counting Wheeler as a zero would have given 20. A column with many NULLs can make an average describe fewer rows than you think.
Step 4 · MIN and MAX. MIN and MAX each pick one value that is already in the column (ringed in blue). They return the value only: the result has no county name, so you cannot tell from it that 10 belongs to Benton.
Step 5 · all in one SELECT. Every aggregate reads the same rows, so they can share one SELECT and still return one row. COUNT(*) says 5 and COUNT(area) says 4, which is how you spot the gap. With no GROUP BY, the whole table is a single group.

Aggregating 1: What’s the average town size in each state?

Scenario: Compare typical town sizes between Oregon and Washington. For each state, report the average town population rounded to a whole number as avg_population.

Aggregating 2: What’s the total urban population by state?

Scenario: How much of each state’s population lives in incorporated towns? For each state, report the summed town population as total_town_population.

Aggregating 3: Summary statistics for county land areas

Scenario: Get a complete statistical overview of county sizes: the count of counties as num_counties, the smallest and largest land areas as smallest_area and largest_area, the average rounded to a whole number as avg_size, and the sum as total_area.

Categorical summaries

MIN and MAX aren’t just for measurements — they also pick out the extremes of columns like year_established (and they even work on text, returning alphabetically first/last values).

Aggregating 4: When were the earliest and most recent counties established?

Scenario: Historical research on regional development. Report the earliest establishment year as earliest_year and the most recent as newest_year.

To find the row behind a minimum or maximum (which town is smallest, which county is oldest), see the subquery examples in Going Further.

Rounding summaries

ROUND helps create cleaner, more readable output for reports and presentations.

Aggregating 5: Calculate and round population density

Scenario: Create a readable density report (people per square mile). Show each town with its state, population_2020_census, land_area_sq_mi, and the density rounded to 1 decimal as people_per_sq_mile.

Aggregating 6: Round to whole numbers for simpler reporting

Scenario: Executive summaries often need whole numbers. For each state, report the average town population as avg_town_pop and the average town land area as avg_town_area, both rounded to whole numbers.