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 of COUNT Expand / Collapse
Author
Message
Posted Friday, January 03, 2014 7:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 09, 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 03, 2014 8:00 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 9:43 AM
Points: 316, Visits: 1,022
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 03, 2014 8:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 09, 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 03, 2014 8:37 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 9:43 AM
Points: 316, Visits: 1,022
Can you post your real code?
Post #1527580
Posted Friday, January 03, 2014 8:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:41 AM
Points: 6,748, Visits: 12,843
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 03, 2014 9:06 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 9:43 AM
Points: 316, Visits: 1,022
Yes - I was wondering...
Post #1527599
Posted Saturday, January 04, 2014 2:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

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

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


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 35,950, Visits: 30,232
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 09, 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 06, 2014 11:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:29 PM
Points: 12,741, Visits: 31,053
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1528201
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse