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.
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 19Cannot 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.countymatchsummaryfrom dbo.Countyreportgroup 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 CLeft 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.countymatchsummaryinsert into opr.dbo.countymatchsummaryselect 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.countymatchsummaryfrom dbo.Countyreportgroup 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 posthttp://sqlerrormessages.blogspot.com/2009/08/sql-server-error-message-msg-130-cannot.html |
|
|
|
|
|
|
|