Summing rows to combine them

• matak

  SSCrazy

  Points: 2674

  More actions

  June 3, 2013 at 11:38 pm

  #278445

  Hi,

  Im trying to find a better way to do this but cant quite get there.

  Here is some quick data setup.

  if OBJECT_ID('tempdb.dbo.#temp') is not null

  drop table #temp;

  create table #temp (

  [state] char(2),

  [county] char(3),

  [sex] varchar(6),

  [class] int,

  [agegroup] varchar(15),

  [total] int

  )

  insert into #temp

  select '01', '001', 'male', 1, '05-09 yrs', 3 union all

  select '01', '001', 'female', 1, '05-09 yrs', 4 union all

  select '01', '001', 'male', 1, '15-17 yrs', 5 union all

  select '01', '001', 'female', 1, '15-17 yrs', 6 union all

  select '01', '001', 'male', 1, '18-20 yrs', 7 union all

  select '01', '001', 'female', 1, '21-24 yrs', 3 union all

  select '01', '001', 'male', 1, '21-24 yrs', 3 union all

  select '01', '001', 'male', 2, '05-09 yrs', 4 union all

  select '01', '001', 'female', 2, '05-09 yrs', 5 union all

  select '01', '001', 'male', 2, '15-17 yrs', 6 union all

  select '01', '001', 'female', 2, '15-17 yrs', 7 union all

  select '01', '001', 'male', 2, '18-20 yrs', 8 union all

  select '01', '001', 'female', 2, '21-24 yrs', 4 union all

  select '01', '001', 'male', 2, '21-24 yrs', 4

  Here is what i currently have. It also gives the result im after

  I was thinking of sum and case but not quite sure where to start or even if its the right way to go.

  select [State], County, Sex, class, '15-24 yrs' AS [Agegroup], sum(total) AS [total]

  from #temp

  where Agegroup in ('15-17 yrs', '18-20 yrs', '21-24 yrs')

  group by [State], County, Sex, class

  union all

  select [State], County, Sex, class, Agegroup, sum(total) AS [total]

  from #temp

  where Agegroup not in ('15-17 yrs', '18-20 yrs', '21-24 yrs')

  group by [State], County, Sex, class, Agegroup

• Mark Cowne

  One Orange Chip

  Points: 26944

  More actions

  June 4, 2013 at 1:35 am

  #1620803

  Another way

  with cte as (

  select [State], County, Sex, class,

  case when Agegroup in ('15-17 yrs', '18-20 yrs', '21-24 yrs') then '15-24 yrs' else Agegroup end AS [Agegroup],

  [total]

  from #temp)

  select [State], County, Sex, class, Agegroup, sum(total) AS [total]

  from cte

  group by [State], County, Sex, class, Agegroup

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• matak

  SSCrazy

  Points: 2674

  More actions

  June 4, 2013 at 1:40 am

  #1620805

  Thanks - this is a lot nicer than what i had.