Sum of an adjacent column within a conditional range

I have a script that is trying to collate some census data.

SELECT tbl.State, tbl.ranges, tbl.race, tbl.origin, tbl.sex
  FROM (select case  
    when age between 18 and 23 then '18-23'
    when age between 24 and 35 then '24-35'
    else '36-85' end as ranges, 
    [State]
      ,race,
      sex,
      origin

  from [StateSexOriginRaceAge]
    group by State, age, pop, race, sex, origin
    having sex > 0
  ) as tbl

group by tbl.State, tbl.ranges, tbl.race, tbl.sex, tbl.origin
order by state, ranges, race, origin, sex

The output is looking roughly like what i want, except there is a population column, and I want to sum the population column for each age bracket. Any thoughts?

I tried

SELECT tbl.State, tbl.ranges, tbl.race, tbl.origin, tbl.sex, tbl.pop
  FROM (select case  
    when age between 18 and 23 then '18-23'
    when age between 24 and 35 then '24-35'
    else '36-85' end as ranges, 
    [State]
      ,race,
      sex,
      origin,
      sum(pop) as pop

  from [StateSexOriginRaceAge]
    group by State, age, pop, race, sex, origin, pop
    having sex > 0
  ) as tbl

group by tbl.State, tbl.ranges, tbl.race, tbl.sex, tbl.origin, tbl.pop
order by state, ranges, race, origin, sex

But I get a ton of extra rows, like 20x as many so it’s not doing what I want, which is to add the sum of the populations of 18-23 and put them there and the sums of the population 24-35.

Thanks for any help!

Answer

You are correct in trying to setup conditional aggregation, but your logic is off. Try this version:

SELECT
    State,
    race,
    sex,
    origin,
    SUM(CASE WHEN age BETWEEN 18 AND 23 THEN pop ELSE 0 END) AS [18-23],
    SUM(CASE WHEN age BETWEEN 24 AND 35 THEN pop ELSE 0 END) AS [24-35],
    SUM(CASE WHEN age BETWEEN 36 AND 85 THEN pop ELSE 0 END) AS [36-85]
FROM [StateSexOriginRaceAge]
GROUP BY
    State,
    race,
    sex,
    origin;

Note that we don’t need any subquery here.

Advertisement

Answer