Skip to content

Instantly share code, notes, and snippets.

@mbforr
Created Jul 25, 2021
Embed
What would you like to do?
WITH
counties AS (
SELECT
a.total_pop,
b.*
FROM
`bigquery-public-data.census_bureau_acs.county_2018_1yr` a
JOIN
`bigquery-public-data.geo_us_boundaries.counties` b
USING
(geo_id) )
SELECT
# first we need to add in the column we want to group by in the query - state_fips_code
state_fips_code,
SUM(total_pop),
AVG(total_pop),
MIN(total_pop),
MAX(total_pop)
FROM
counties
# next, we add group by to the query to group the aggregations by that column
GROUP BY
state_fips_code
# we can also order by the aggregation as well, in the cases the sum
ORDER BY
SUM(total_pop) DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment