agg_group.sql

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