Post reply

SUM of COUNT

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

  • 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 ;

  • 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

  • January 3, 2014 at 8:37 am

    #1677319

    Can you post your real code?

  • 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

  • January 3, 2014 at 9:06 am

    #1677336

    Yes - I was wondering...

  • January 4, 2014 at 2:32 am

    #1677494

    Hi, can anyone help here?

    Thanks!!

  • 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)

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

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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply