Another Error!!!!! - 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

General SQL Server Forums

New to SQL Server Programming

Another Error!!!!!

Author

Topic

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-02-13 : 13:45:08

Please help with this one!!!!!!
What I'm trying to do is do a case statement, but when I did what I did, this post an error:

Server: Msg 130, Level 15, State 1, Line 19
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

My codes:

select COUNTY,SVCMO,servicemonth
,case
when type in ('med') then 'Medicaid'
when type in ('Bi_Co COD Residential') then 'Bi_Co COD Residential'
when type in ('Therapeutic Childcare') then 'Therapeutic Childcare'
when type in ('SCHIP')then 'SCHIP'
when type in ('state') then 'State'
when type in ('Detox R & B')then 'Detox R & B'
when type in ('Bi_Co COD R & B') then 'Bi_Co COD R & B'

end as Type
,sum(case

when type in (select type from huynhtl.tblmatch) and svcmo in (select begindate,enddate from huynhtl.tblmatch)
then (reimb * (select rate from huynhtl.tblmatch))

end ) as Totals
--into dbo.countymatchsummary
from dbo.Countyreport
group by COUNTY,SVCMO,servicemonth
,case
when type in ('med') then 'Medicaid'
when type in ('Bi_Co COD Residential') then 'Bi_Co COD Residential'
when type in ('Therapeutic Childcare') then 'Therapeutic Childcare'
when type in ('SCHIP')then 'SCHIP'
when type in ('state') then 'State'
when type in ('Detox R & B')then 'Detox R & B'
when type in ('Bi_Co COD R & B') then 'Bi_Co COD R & B'

end

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-13 : 13:53:25

Using Selects in an IN clause is a bad idea because it performs really poorly. Try this out:


.
.
.
Sum (Case When M.type Is Not Null Then C.reimb * M.rate Else 0 End) As Totals
.
.
.
From dbo.Countyreport C
Left Outer Join huynhtl.tblmatch M 
	On C.type = M.type 
	--I'm assuming you want svcmo to be 
	--between the start and end dates
	And C.svcmo Between begindate And enddate

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-02-13 : 14:32:39

This is the original codes, I'm not sure if this what you indicate would have the same results.

truncate table opr.dbo.countymatchsummary
insert into opr.dbo.countymatchsummary

select COUNTY,SVCMO,servicemonth
,case
when type in ('med') then 'Medicaid'
when type in ('Bi_Co COD Residential') then 'Bi_Co COD Residential'
when type in ('Therapeutic Childcare') then 'Therapeutic Childcare'
when type in ('SCHIP')then 'SCHIP'
when type in ('state') then 'State'
when type in ('Detox R & B')then 'Detox R & B'
when type in ('Bi_Co COD R & B') then 'Bi_Co COD R & B'

end as Type
,sum(case
when type in ('med') and svcmo <= 200709 then (reimb * .4988)
end ) as Totals
--into dbo.countymatchsummary
from dbo.Countyreport
group by COUNTY,SVCMO,servicemonth
,case
when type in ('med') then 'Medicaid'
when type in ('Bi_Co COD Residential') then 'Bi_Co COD Residential'
when type in ('Therapeutic Childcare') then 'Therapeutic Childcare'
when type in ('SCHIP')then 'SCHIP'
when type in ('state') then 'State'
when type in ('Detox R & B')then 'Detox R & B'
when type in ('Bi_Co COD R & B') then 'Bi_Co COD R & B'

end

huynhtl
Posting Yak Master

107 Posts

Posted - 2008-02-13 : 16:31:55

Qualis,
I tried your way and it work. The only concern I have is that my number doesn't match from July to September. From October forward,it matches to the pennies.

Any suggestion?

shijobaby
Starting Member

44 Posts

Posted - 2009-08-21 : 06:22:48

The ways to avoid this error is simple

just look into my post

http://sqlerrormessages.blogspot.com/2009/08/sql-server-error-message-msg-130-cannot.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources