Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Mar 2010
    Posts
    32

    Unanswered: How Can I Perform Multiple Counts in One Query?

    This should be easy to do, but my query won't work

    I need a separate count for each agency:

    Code:
    
    SELECT  
    (
    count(RECIP_SSN_NBR)
    FROM     DSNP.PR01_T_RECIP_SYS
    WHERE    RECIP_RETIR_DT <= '2010-02-01' 
    AND      AGTY_SYS_CD = 'TAPERS'
    and benef_stat_cd = 'AC'
    and benef_seq_nbr = 1)  as "TOTAL TAPERS",
    
    
    
    (select count(RECIP_SSN_NBR) 
    FROM     DSNP.PR01_T_RECIP_SYS 
    WHERE    RECIP_RETIR_DT <= '2010-02-01' 
    AND      AGTY_SYS_CD = 'TASPRS'
    and benef_stat_cd = 'AC'
    and benef_seq_nbr = 1) as "TOTAL TASPRS"

    Can anyone tell me why this query errors out?

    I also tried:

    Code:
    SELECT  
    (
    count(RECIP_SSN_NBR)
    FROM     DSNP.PR01_T_RECIP_SYS
    WHERE    RECIP_RETIR_DT <= '2010-02-01' 
    AND      AGTY_SYS_CD = 'TAPERS'
    and benef_stat_cd = 'AC'
    and benef_seq_nbr = 1)  as "TOTAL TAPERS",
    
    
    
    
    (select count(RECIP_SSN_NBR) 
    FROM     DSNP.PR01_T_RECIP_SYS 
    WHERE    RECIP_RETIR_DT <= '2010-02-01' 
    AND      AGTY_SYS_CD = 'TASPRS'
    and benef_stat_cd = 'AC'
    and benef_seq_nbr = 1)  as "TOTAL TASPRS"
    
    FROM DSNP.PR01_T_RECIP_SYS

    This fails as well. What am I doing wrong???

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try this:

    Code:
    SELECT  AGTY_SYS_CD,count(RECIP_SSN_NBR)
    FROM     DSNP.PR01_T_RECIP_SYS
    WHERE    RECIP_RETIR_DT <= '2010-02-01' 
    and benef_stat_cd = 'AC'
    and benef_seq_nbr = 1
    group by AGTY_SYS_CD
    order by AGTY_SYS_CD
    Andy

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What error message(s) did you get, especialy for the second query?

    At least, SELECT keyword is necessary.
    And, FROM sysibm.sysdummy1 is necessary for the first query.
    Code:
     
    SELECT  
    (SELECT
    count(RECIP_SSN_NBR)
    FROM     DSNP.PR01_T_RECIP_SYS
    .....
    .....
     
    FROM sysibm.sysdummy1

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Smile What is a problem ?

    What is a problem ?

    Code:
    SELECT  "TOTAL TAPERS", "TOTAL TASPRS"
    FROM
    
    (select count(RECIP_SSN_NBR) as "TOTAL TAPERS"
    FROM     DSNP.PR01_T_RECIP_SYS
    WHERE    
    RECIP_RETIR_DT     <= '2010-02-01' 
    AND AGTY_SYS_CD   = 'TAPERS'
    and benef_stat_cd    = 'AC'
    and benef_seq_nbr    = 1    )  part_1
    
    JOIN
    
    (select count(RECIP_SSN_NBR) as "TOTAL TASPRS"
    FROM     DSNP.PR01_T_RECIP_SYS 
    WHERE    
    RECIP_RETIR_DT   <= '2010-02-01' 
    AND AGTY_SYS_CD = 'TASPRS'
    and benef_stat_cd  = 'AC'
    and benef_seq_nbr  = 1    )  part_2 
    
    On 1 = 1
    Lenny

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs up More effective query

    You can use, also, more effective query:

    Code:
    Select 
    SUM(case when AGTY_SYS_CD   = 'TAPERS' then 1 else 0 end) 
    "TOTAL TAPERS", 
    SUM(case when AGTY_SYS_CD   = 'TASPRS' then 1 else 0 end) 
    "TOTAL TASPRS"
    FROM   DSNP.PR01_T_RECIP_SYS
    WHERE    
    RECIP_RETIR_DT     <= '2010-02-01' 
    AND AGTY_SYS_CD   in ( 'TAPERS', 'TASPRS')
    and benef_stat_cd    = 'AC'
    and benef_seq_nbr    = 1
    Lenny

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can replace
    SUM(case when AGTY_SYS_CD = 'TAPERS' then 1 else 0 end) "TOTAL TAPERS"

    with
    COUNT(case AGTY_SYS_CD when 'TAPERS' then RECIP_SSN_NBR end) "TOTAL TAPERS"

    or if RECIP_SSN_NBR is not null, then
    COUNT(case AGTY_SYS_CD when 'TAPERS' then 0 end) "TOTAL TAPERS"

    Please refer to
    COUNT(expression) aggregate function
    and
    simple-when-clause of CASE expression
    in manual "SQL Reference".

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Exclamation count(RECIP_SSN_NBR) = count(*)

    Quote Originally Posted by tonkuma View Post
    You can replace
    SUM(case when AGTY_SYS_CD = 'TAPERS' then 1 else 0 end) "TOTAL TAPERS"

    with
    COUNT(case AGTY_SYS_CD when 'TAPERS' then RECIP_SSN_NBR end) "TOTAL TAPERS"

    or if RECIP_SSN_NBR is not null, then
    COUNT(case AGTY_SYS_CD when 'TAPERS' then 0 end) "TOTAL TAPERS"

    Please refer to
    COUNT(expression) aggregate function
    and
    simple-when-clause of CASE expression
    in manual "SQL Reference".
    Hi, tonkuma !

    In very first version author used count(RECIP_SSN_NBR), but in real we have no difference between count(RECIP_SSN_NBR) and count(*), so, you can't use COUNT(case AGTY_SYS_CD when 'TAPERS' then RECIP_SSN_NBR end) because you'll get the wrong result.

    See following example which I create to especially for this question:

    Code:
    select count(RECIP_SSN_NBR)
    from
    ( 
    select 100   as RECIP_SSN_NBR
    from sysibm.sysdummy1
    union all
    select 100 as RECIP_SSN_NBR
    from sysibm.sysdummy1
    union all
    select 100 as RECIP_SSN_NBR
    from sysibm.sysdummy1
    union all
    select 700 as RECIP_SSN_NBR 
    from sysibm.sysdummy1
    ) test
    compare result to

    Code:
    select count(*)
    from
    ( 
    select 100   as RECIP_SSN_NBR
    from sysibm.sysdummy1
    union all
    select 100 as RECIP_SSN_NBR
    from sysibm.sysdummy1
    union all
    select 100 as RECIP_SSN_NBR
    from sysibm.sysdummy1
    union all
    select 700 as RECIP_SSN_NBR 
    from sysibm.sysdummy1
    ) test
    Lenny

  8. #8
    Join Date
    Mar 2010
    Posts
    32
    Quote Originally Posted by Lenny77 View Post
    What is a problem ?

    Code:
    SELECT  "TOTAL TAPERS", "TOTAL TASPRS"
    FROM
    
    (select count(RECIP_SSN_NBR) as "TOTAL TAPERS"
    FROM     DSNP.PR01_T_RECIP_SYS
    WHERE    
    RECIP_RETIR_DT     <= '2010-02-01' 
    AND AGTY_SYS_CD   = 'TAPERS'
    and benef_stat_cd    = 'AC'
    and benef_seq_nbr    = 1    )  part_1
    
    JOIN
    
    (select count(RECIP_SSN_NBR) as "TOTAL TASPRS"
    FROM     DSNP.PR01_T_RECIP_SYS 
    WHERE    
    RECIP_RETIR_DT   <= '2010-02-01' 
    AND AGTY_SYS_CD = 'TASPRS'
    and benef_stat_cd  = 'AC'
    and benef_seq_nbr  = 1    )  part_2 
    
    On 1 = 1
    Lenny

    Wow, thanks to everyone for responding. Great suggestions. Yours is really interesting here, Lenny. Never seen it coded this way. I understand the concept, but the syntax is unfamiliar.

    Can you show me how to code the above query if I am counting on 3 different agencies instead of two? Something like:

    Code:
    
    SELECT  "TOTAL TAPERS", "TOTAL TASPRS", "TOTAL TAJRS"
    FROM
    
    (select count(RECIP_SSN_NBR) as "TOTAL TAPERS"
    FROM     DSNP.PR01_T_RECIP_SYS
    WHERE    
    RECIP_RETIR_DT     <= '2010-02-01' 
    AND AGTY_SYS_CD   = 'TAPERS'
    and benef_stat_cd    = 'AC'
    and benef_seq_nbr    = 1    )  part_1
    
     JOIN
    
    (select count(RECIP_SSN_NBR) as "TOTAL TASPRS"
    FROM     DSNP.PR01_T_RECIP_SYS 
    WHERE    
    RECIP_RETIR_DT   <= '2010-02-01' 
    AND AGTY_SYS_CD = 'TASPRS'
    and benef_stat_cd  = 'AC'
    and benef_seq_nbr  = 1    )  part_2 
    
    JOIN
    (select count(RECIP_SSN_NBR) as "TOTAL TAJRS"
    FROM     DSNP.PR01_T_RECIP_SYS 
    WHERE    
    RECIP_RETIR_DT   <= '2010-02-01' 
    AND AGTY_SYS_CD = 'TAJRS'
    and benef_stat_cd  = 'AC'
    and benef_seq_nbr  = 1    )  part_3 
    
    On 1 = 1
    and ON 1 = 3

    Of course, this errors out for me. Argh.

  9. #9
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    This seems like very complicated way to do what ARWinner already gave you. Though what he gave you was for all AGTY_SYS_CD's, you can modify it to only give for the AGTY_SYS_CD's you are interested in like:
    Code:
    select AGTY_SYS_CD,count(RECIP_SSN_NBR) 
    FROM     DSNP.PR01_T_RECIP_SYS 
    WHERE    
    RECIP_RETIR_DT   <= '2010-02-01' 
    AND AGTY_SYS_CD in ( 'TAJRS','TAPERS','TASPRS'  --any others???)
    and benef_stat_cd  = 'AC'
    and benef_seq_nbr  = 1
    group by AGTY_SYS_CD
    Dave

  10. #10
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by dvdaddict32 View Post
    Wow, thanks to everyone for responding. Great suggestions. Yours is really interesting here, Lenny. Never seen it coded this way. I understand the concept, but the syntax is unfamiliar.

    Can you show me how to code the above query if I am counting on 3 different agencies instead of two? Something like:

    Code:
    
    SELECT  "TOTAL TAPERS", "TOTAL TASPRS", "TOTAL TAJRS"
    FROM
    
    (select count(RECIP_SSN_NBR) as "TOTAL TAPERS"
    FROM     DSNP.PR01_T_RECIP_SYS
    WHERE    
    RECIP_RETIR_DT     <= '2010-02-01' 
    AND AGTY_SYS_CD   = 'TAPERS'
    and benef_stat_cd    = 'AC'
    and benef_seq_nbr    = 1    )  part_1
    
     JOIN
    
    (select count(RECIP_SSN_NBR) as "TOTAL TASPRS"
    FROM     DSNP.PR01_T_RECIP_SYS 
    WHERE    
    RECIP_RETIR_DT   <= '2010-02-01' 
    AND AGTY_SYS_CD = 'TASPRS'
    and benef_stat_cd  = 'AC'
    and benef_seq_nbr  = 1    )  part_2 
    
    JOIN
    (select count(RECIP_SSN_NBR) as "TOTAL TAJRS"
    FROM     DSNP.PR01_T_RECIP_SYS 
    WHERE    
    RECIP_RETIR_DT   <= '2010-02-01' 
    AND AGTY_SYS_CD = 'TAJRS'
    and benef_stat_cd  = 'AC'
    and benef_seq_nbr  = 1    )  part_3 
    
    On 1 = 1
    and ON 1 = 3

    Of course, this errors out for me. Argh.
    Add one more JOIN with ON 1 = 1, etc

    and ON 1 = 3 - is mistake, because result is FALSE.... Also you can't use AND with that design....
    Always 1 = 1, or 100 = 100, or something = something because of TRUE....

    Lenny

    P.S. Also
    select cnt1, cnt2, cnt2... from part1 JOIN part2 on 1 = 1 JOIN part3 on 1 = 1... etc
    Last edited by Lenny77; 03-03-10 at 17:19.

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by dvdaddict32 View Post
    Wow, thanks to everyone for responding. Great suggestions. Yours is really interesting here, Lenny. Never seen it coded this way. I understand the concept, but the syntax is unfamiliar.

    Can you show me how to code the above query if I am counting on 3 different agencies instead of two? Something like:

    Code:
    
    SELECT  "TOTAL TAPERS", "TOTAL TASPRS", "TOTAL TAJRS"
    FROM
    
    (select count(RECIP_SSN_NBR) as "TOTAL TAPERS"
    FROM     DSNP.PR01_T_RECIP_SYS
    WHERE    
    RECIP_RETIR_DT     <= '2010-02-01' 
    AND AGTY_SYS_CD   = 'TAPERS'
    and benef_stat_cd    = 'AC'
    and benef_seq_nbr    = 1    )  part_1
    
     JOIN
    
    (select count(RECIP_SSN_NBR) as "TOTAL TASPRS"
    FROM     DSNP.PR01_T_RECIP_SYS 
    WHERE    
    RECIP_RETIR_DT   <= '2010-02-01' 
    AND AGTY_SYS_CD = 'TASPRS'
    and benef_stat_cd  = 'AC'
    and benef_seq_nbr  = 1    )  part_2 
    
    JOIN
    (select count(RECIP_SSN_NBR) as "TOTAL TAJRS"
    FROM     DSNP.PR01_T_RECIP_SYS 
    WHERE    
    RECIP_RETIR_DT   <= '2010-02-01' 
    AND AGTY_SYS_CD = 'TAJRS'
    and benef_stat_cd  = 'AC'
    and benef_seq_nbr  = 1    )  part_3 
    
    On 1 = 1
    and ON 1 = 3

    Of course, this errors out for me. Argh.

    The "ON 1 = 1" is the join predicate, which just happens to be always true. A join itself has the syntax:

    <table> JOIN <table> ON <condition>

    The result of a join is a table. Since you can join a table with something else if you like, you can do this:

    <table> JOIN <table> ON 1 = 1 JOIN <table> ON 1 = 1
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Lenny77 View Post
    but in real we have no difference between count(RECIP_SSN_NBR) and count(*)
    Uhm... that is not entirely correct...

    Code:
    with t(a) as ( values 
      ('a'), 
      (cast (null as char(1))), 
      ('c')) 
    select count(a), count(*) from t
    
    1           2
    ----------- -----------
              2           3
    ---
    "It does not work" is not a valid problem statement.

  13. #13
    Join Date
    Mar 2010
    Posts
    32
    Quote Originally Posted by dav1mo View Post
    This seems like very complicated way to do what ARWinner already gave you. Though what he gave you was for all AGTY_SYS_CD's, you can modify it to only give for the AGTY_SYS_CD's you are interested in like:
    Code:
    select AGTY_SYS_CD,count(RECIP_SSN_NBR) 
    FROM     DSNP.PR01_T_RECIP_SYS 
    WHERE    
    RECIP_RETIR_DT   <= '2010-02-01' 
    AND AGTY_SYS_CD in ( 'TAJRS','TAPERS','TASPRS'  --any others???)
    and benef_stat_cd  = 'AC'
    and benef_seq_nbr  = 1
    group by AGTY_SYS_CD
    Dave
    Hi Dave,

    You are right. ARWINNER is the easiest. Guess I just like to do things the hard way! lol

    Thanks again.

  14. #14
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by n_i View Post
    Uhm... that is not entirely correct...

    Code:
    with t(a) as ( values 
      ('a'), 
      (cast (null as char(1))), 
      ('c')) 
    select count(a), count(*) from t
    
    1           2
    ----------- -----------
              2           3
    Agree !

    Lenny

  15. #15
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by dvdaddict32 View Post
    Hi Dave,

    You are right. ARWINNER is the easiest. Guess I just like to do things the hard way! lol

    Thanks again.
    It will not work if we'll have the different conditions on where statement....

    But in your case it is a possible to use, if you don't need the different names for different counters.

    Lenny

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •