SUM of COUNT

• nasy_mcs

  SSC Enthusiast

  Points: 172

  More actions

  January 3, 2014 at 7:51 am

  #148413

  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!!

• laurie-789651

  SSCertifiable

  Points: 7680

  More actions

  January 3, 2014 at 8:00 am

  #1677292

  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 ;

• nasy_mcs

  SSC Enthusiast

  Points: 172

  More actions

  January 3, 2014 at 8:30 am

  #1677311

  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

• laurie-789651

  SSCertifiable

  Points: 7680

  More actions

  January 3, 2014 at 8:37 am

  #1677319

  Can you post your real code?

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 3, 2014 at 8:59 am

  #1677331

  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

• laurie-789651

  SSCertifiable

  Points: 7680

  More actions

  January 3, 2014 at 9:06 am

  #1677336

  Yes - I was wondering...

• nasy_mcs

  SSC Enthusiast

  Points: 172

  More actions

  January 4, 2014 at 2:32 am

  #1677494

  Hi, can anyone help here?

  Thanks!!

• Jeff Moden

  SSC Guru

  Points: 1004591

  More actions

  January 4, 2014 at 12:29 pm

  #1677546

  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.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• nasy_mcs

  SSC Enthusiast

  Points: 172

  More actions

  January 6, 2014 at 10:20 am

  #1677819

  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!!

• Lowell

  SSC Guru

  Points: 323522

  More actions

  January 6, 2014 at 11:19 am

  #1677843

  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 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!