Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Summing rows to combine them Expand / Collapse
Author
Message
Posted Monday, June 03, 2013 11:38 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 1:06 AM
Points: 199, Visits: 1,554
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

Post #1459547
Posted Tuesday, June 04, 2013 1:35 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:13 AM
Points: 1,694, Visits: 19,550

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



____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1459579
Posted Tuesday, June 04, 2013 1:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 1:06 AM
Points: 199, Visits: 1,554
Thanks - this is a lot nicer than what i had.
Post #1459581
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse