Aggregating Techniques

Numerical summaries

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

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: Find the smallest incorporated town

Scenario: A journalist wants to profile the smallest town in the Pacific Northwest. Show the town with its state and population_2020_census.

Aggregating 4: Find the largest city

Scenario: Identify the region’s largest metropolitan center. Show the town with its state and population_2020_census.

Aggregating 5: 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 6: 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.

Aggregating 7: Find the actual earliest and newest counties

Scenario: Get the county names, not just the years. Show each county with its state and year_established.

Rounding summaries

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

Aggregating 8: 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 9: 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.