• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 427
  • Last Modified:

MS SQL - Using SUM and CASE to get counts


I am using SUM (CASE... to get counts.   The query does not seem to return the correct values.  When I do just  Select COUNT(*) from Table Where (Single criteria), it returns a different value.

What am I doing wrong here?

 SUM(CASE WHEN (DMStype = 'ADP' And PollStatus = 'Complete') then 1 else 0 end) AS ADPcomp,
 SUM(CASE WHEN (DMStype = 'ADP') then 1 else 0 end) AS ADPtot,
 SUM(CASE WHEN (DMStype IN ('RR','RRC') And PollStatus = 'Complete') then 1 else 0 end) AS RRComp,
 SUM(CASE WHEN (DMStype IN ('RR','RRC')) then 1 else 0 end) AS RRtot
From MyTable
Where DMStype IN ('ADP','RR','RRC') And DealerStatus IN ('Prod','IPRO','Stage')

Open in new window

Tom Sage
Tom Sage
3 Solutions
The query looks ok with me.

Can you post the count query you compared the results with?
Lines 3 and 5 are returning wrong sum. Isn't it?
Most likely you forgot about the condition:
Where DMStype IN ('ADP','RR','RRC') And DealerStatus IN ('Prod','IPRO','Stage')
So you are expecting the sum of all the rows of 'ADP', 'RR', and 'RRC' but the condition DealerStatus IN ('Prod','IPRO','Stage') will cut the results down which confuses you.
The querylooks good to me.And the most likely issue is while running the select count(*),you might have missed the dealer status criteria.

hope this helps.
Tom SageAuthor Commented:
Thanks to everyone.   The counts are working ok.  My mistake.  Sorry.

Thank you
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now