Sorting and Grouping Techniques
📚 Schema reference · towns & counties 3 tables
- fips
- name
- state
- county_id
- county
- state
- fips_short
- county_seat
- county_seat_town_id
- year_established
- origin
- etymology
- population_2022
- land_area_sq_mi
- 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
Sorting
ORDER BY controls how results are displayed. This is crucial for reports and identifying extremes.
Sorting 1: Rank towns by population (smallest first)
Scenario: Default ORDER BY sorts ascending (smallest to largest). Show town, state, and population_2020_census.
Sorting 2: Rank towns by population (largest first)
Scenario: DESC reverses the order for “top N” style reports. Show town, state, and population_2020_census.
Sorting 3: Sort by multiple columns (state, then population within state)
Scenario: Create a report organized by state, with largest cities first within each state. Show town, state, and population_2020_census.
Sorting 4: Sort counties by age (oldest first)
Scenario: Historical timeline of county formation. Show each county with its state, year_established, and etymology.
Grouping
GROUP BY collapses rows into groups for aggregate calculations. This is one of SQL’s most powerful features.
Grouping 1: Count towns per state
Scenario: Basic comparison of how many incorporated towns each state has. For each state, report the count as number_of_towns.
Grouping 2: Compare states by average county population
Scenario: Which state has larger counties on average? For each state, show the count of counties as num_counties, the average county population (rounded to a whole number) as avg_county_pop, and the summed population as total_state_pop.
Grouping 3: Which counties have the most towns?
Scenario: Identify counties with many incorporated municipalities. For each primary_county and state, report the town count as num_towns and the combined town population as total_pop.
Grouping 4: Filter groups with HAVING
Scenario: Find only counties with 10 or more towns. HAVING filters after grouping (WHERE filters before). Show each primary_county and state with its town count as num_towns.
First, let’s see why WHERE doesn’t work here:
Use HAVING instead to filter grouped results:
Grouping 5: Population growth by county
Scenario: Which counties saw the most population growth in their towns from 2010 to 2020? For each primary_county and state, report summed town populations as pop_2010 and pop_2020, plus the difference as growth.