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

SUM Case statement where COUNT inside CASE statement Expand / Collapse
Author
Message
Posted Monday, October 5, 2009 1:11 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 17, 2012 6:16 AM
Points: 89, Visits: 236
hi,

Need some expertice or advice,

I am trying to SUM my case statement where I have COUNT function inside the CASE statement.
Here is my sample code:
SELECT d.name,
CASE WHEN a.procedure_code LIKE '%F' THEN COUNT(DISTINCT a.ID) ELSE 0 END AS NumberOf_Code,
SUM(DISTINCT c.number_claims) AS number_claims,
COUNT(DISTINCT c.filename) AS number_files
FROM TableOne AS a
INNER JOIN TableTwo AS b with (Nolock)
ON a.claim_id = b.id
INNER JOIN TableThree AS c with (Nolock)
ON b.transaction_id = c.id
INNER JOIN TableFour AS d with (Nolock)
ON c.provider_id = d.id
GROUP BY d.name,a.procedure_code

Now, if i run the query I am getting duplicate Row and I know its due to not summing up the other columns. So, I tried to put my CASE statement in SUM ( Case......) AS NumberOf_Code but the sql giving me an error "Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

There is several way to achive the goal and the one I know is to put this data into temp table and use temp table for further group by but that will aditional step< I am thinking to do achive this result in one single query...

Any thought would be greatly appriciated.
Post #798131
Posted Monday, October 5, 2009 1:20 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
Go with the temp table idea. It's the best option.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #798143
Posted Monday, October 5, 2009 7:55 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
Or, how about this? The cte does the grouping like a temp table, but without any logging overhead.

;with cte as ( 
SELECT d.name,
COUNT(DISTINCT a.ID) AS DistinctIDs,
SUM(DISTINCT c.number_claims) AS number_claims,
COUNT(DISTINCT c.filename) AS number_files
FROM TableOne AS a
INNER JOIN TableTwo AS b with (Nolock)
ON a.claim_id = b.id
INNER JOIN TableThree AS c with (Nolock)
ON b.transaction_id = c.id
INNER JOIN TableFour AS d with (Nolock)
ON c.provider_id = d.id
GROUP BY d.name,a.procedure_code
)

select d.name,
CASE WHEN a.procedure_code LIKE '%F' THEN DistinctIDs ELSE 0 END AS NumberOf_Code,
number_claims,number_files
from cte



__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #798274
Posted Wednesday, October 7, 2009 2:14 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 17, 2012 6:16 AM
Points: 89, Visits: 236
Bob -Thanks a million...it work...Awsome...I appriciated....The only thing i have to do is i have to tiwic a query littlebit and one single change and it worked perfectly...thanks a lot bcz I didnt want to create a temp table so..it work perfect for my Need...

Thanks a Billionn..
Post #799508
Posted Wednesday, October 7, 2009 2:38 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
No problem. It's not *that* big a deal, but you're welcome.

CTEs are our friends.


__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #799528
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse