Group by problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Group by problem

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2003-04-25 : 16:58:39

I have a table look like:

Year Subtype counts
2002 4 2
2002 5 231
2002 6 1218
2002 7 132
2003 1 1
2003 4 3
2003 5 59
2003 6 290
2003 7 44

And, trying to group by subtype. the result should look like:

Year Subtype Counts
2002 Indemity 233
2002 Info 132
2002 Medical 1218
2003 Indemity 63
2003 Info 44
2003 Medical 290

I have used following SQL to get the above result.

Begin
select year(clm_incdate)as injyear,clm_subtype as subtype,count(claim_key) as claimcount
into #temp1
from claim,loc_xref
where
claim.ref_location = loc_xref.location_key and
re_loc1 = 231456

Group by year(clm_incdate),clm_subtype

order by year(clm_incdate)
End

Begin
select injyear,
(Case when subtype = 7 then 'Info'
when subtype = 6 then 'Medical'
when subtype < 6 then 'Indemity'
Else 'X'End) As subtype
,claimcount

from
#temp1

Group by
injyear,
Case when subtype = 7 then 'Info'
when subtype = 6 then 'Medical'
when subtype < 6 then 'Indemity'
Else 'X' End,claimcount

End

But the result look like this:

Year subtype counts
2002 Indemity 2
2002 Indemity 231
2002 Info 132
2002 Medical 1218
2003 Indemity 1
2003 Indemity 3
2003 Indemity 59
2003 Info 44
2003 Medical 290

I am not sure what I am doing wrong.Any help would be appreciated it..

Thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-25 : 17:15:43

Don't group on the claimcount field ... and I'm not sure why you are using a temp table.

- Jeff

Edited by - jsmith8858 on 04/25/2003 17:16:02

claire
Starting Member

19 Posts

Posted - 2003-04-26 : 03:12:36

Try this

create table table1(Year int,Subtype int,counts int)
go
insert into table1 select 2002,4,2
insert into table1 select 2002,5,231
insert into table1 select 2002,6,1218
insert into table1 select 2002,7,132
insert into table1 select 2003,1,1
insert into table1 select 2003,4,3
insert into table1 select 2003,5,59
insert into table1 select 2003,6,290
insert into table1 select 2003,7,44
go

---------------------------------------------------

select T.year,T.subtype,sum(T.counts) from
(select year,
(Case when subtype = 7 then 'Info'
when subtype = 6 then 'Medical'
when subtype < 6 then 'Indemity'
Else 'X' End) As subtype
,sum(counts) as counts
from table1
Group by
year,subtype ) as T
group by T.year,T.subtype order by T.year

go

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources