?
Solved

MS SQL - Using SUM and CASE to get counts

Posted on 2011-03-01
4
Medium Priority
?
415 Views
Last Modified: 2012-05-11
Hello,

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?

Thanks
SELECT 
 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

0
Comment
Question by:Tom Sage
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 22

Accepted Solution

by:
Thomasian earned 668 total points
ID: 35014026
The query looks ok with me.

Can you post the count query you compared the results with?
0
 
LVL 24

Assisted Solution

by:jimyX
jimyX earned 668 total points
ID: 35014154
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.
0
 

Assisted Solution

by:pavan_kumar_kokkiragedda
pavan_kumar_kokkiragedda earned 664 total points
ID: 35014755
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.
output
Regards,
Pavan
0
 

Author Closing Comment

by:Tom Sage
ID: 35016568
Thanks to everyone.   The counts are working ok.  My mistake.  Sorry.

Thank you
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question