Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SUM of COUNT Expand / Collapse
Author
Message
Posted Friday, January 3, 2014 7:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 9, 2014 9:20 AM
Points: 4, Visits: 10
Hi, I want SUM of count of the number of records available in database for first WHEN in below query. For example if i have 2 records in database for first case(prd_cd='1') , 5 for second case(prd_cd='2') and 10 for third case(pr_cd='3') then I should get 17 as sum of count in diplay. Can someone help?

select CASE
WHEN prd_cd='1' or prd_cd='2' or pr_cd='3' THEN 'PROCESSED' <---- I am looking for this only
WHEN prd_cd='X' THEN ' NOT PROCESSED'
ELSE 'UNKNOWN' END AS "Key Check Points", count(*) as "Rules"
from Table 1 where date(crt_ts) = current date
group by prd_cd with ur ;


THanks!!
Post #1527547
Posted Friday, January 3, 2014 8:00 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, September 29, 2016 5:30 AM
Points: 424, Visits: 1,271
select CASE
WHEN prd_cd='1' or prd_cd='2' or pr_cd='3' THEN 'PROCESSED' <---- I am looking for this only
WHEN prd_cd='X' THEN ' NOT PROCESSED'
ELSE 'UNKNOWN' END AS "Key Check Points", count(*) as "Rules",
SUM(CASE WHEN prd_cd='1' or prd_cd='2' or pr_cd='3' THEN 1 ELSE 0 END) as YourCount
from Table 1 where date(crt_ts) = current date
group by prd_cd with ur ;

Post #1527553
Posted Friday, January 3, 2014 8:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 9, 2014 9:20 AM
Points: 4, Visits: 10
Laurie, I am getting below error. Also I am looking for SUM of COUNT not SUM of fields.

104: SQL0104N An unexpected token "SUM" was found following "". Expected tokens may include: ", FROM INTO". SQLSTATE=42601
Post #1527572
Posted Friday, January 3, 2014 8:37 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, September 29, 2016 5:30 AM
Points: 424, Visits: 1,271
Can you post your real code?
Post #1527580
Posted Friday, January 3, 2014 8:59 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 10:11 AM
Points: 8,587, Visits: 18,753
It's a DB2 error, Laurie

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1527594
Posted Friday, January 3, 2014 9:06 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, September 29, 2016 5:30 AM
Points: 424, Visits: 1,271
Yes - I was wondering...
Post #1527599
Posted Saturday, January 4, 2014 2:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 9, 2014 9:20 AM
Points: 4, Visits: 10
Hi, can anyone help here?

Thanks!!
Post #1527791
Posted Saturday, January 4, 2014 12:29 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:23 PM
Points: 42,036, Visits: 39,415
nasy_mcs (1/4/2014)
Hi, can anyone help here?

Thanks!!


Add a WHERE clause that limits prd_cd to only those values that you test for in the CASE statements and see if that helps return the answer you're looking for.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

Helpful Links:
How to post code problems
How to post performance problems
Post #1527853
Posted Monday, January 6, 2014 10:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 9, 2014 9:20 AM
Points: 4, Visits: 10
Hi Jeff,

I have lot of WHEN condition in the query, I have provided two conditions only in my example.
Putting WHERE (prd_cd='1' or prd_cd='2' or pr_cd='3' ) will not be correct?

Thanks!!
Post #1528175
Posted Monday, January 6, 2014 11:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:39 PM
Points: 14,540, Visits: 38,379
here's my first guess on what you are after...some SUM(CASE statements to get your subtotals:

SELECT SUM(CASE
WHEN prd_cd='1' OR prd_cd='2' OR pr_cd='3'
THEN 1
ELSE 0
END) AS Processed,
SUM(CASE
WHEN prd_cd='X'
THEN 1
ELSE 0
END) AS NotProcessed,
SUM(CASE
WHEN prd_cd='1' OR prd_cd='2' OR pr_cd='3' or prd_cd='X'
THEN 0
ELSE 1
END) As UnKown,
COUNT(*) AS Rules
FROM [TABLE 1] WHERE CONVERT(date,crt_ts) = CONVERT(date,getdate())
GROUP BY prd_cd;




Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1528201
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse