How Can I Perform Multiple Counts in One Query?

dvdaddict32 · #1 (**permalink**) 03-03-10, 11:43

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

ARWinner · #2 (**permalink**) 03-03-10, 11:55

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

tonkuma · #3 (**permalink**) 03-03-10, 12:04

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

Lenny77 · #4 (**permalink**) 03-03-10, 12:21

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

Lenny77 · #5 (**permalink**) 03-03-10, 12:33

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

tonkuma · #6 (**permalink**) 03-03-10, 12:51

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".

Lenny77 · #7 (**permalink**) 03-03-10, 14:41

Quote:

Originally Posted by tonkuma

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

dvdaddict32 · #8 (**permalink**) 03-03-10, 15:25

Quote:

Originally Posted by Lenny77

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.

dav1mo · #9 (**permalink**) 03-03-10, 16:01

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

Lenny77 · #10 (**permalink**) 03-03-10, 16:09

Quote:

Originally Posted by dvdaddict32

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

stolze · #11 (**permalink**) 03-03-10, 16:11

Quote:

Originally Posted by dvdaddict32

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

n_i · #12 (**permalink**) 03-03-10, 16:11

Quote:

Originally Posted by Lenny77

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

dvdaddict32 · #13 (**permalink**) 03-03-10, 16:22

Quote:

Originally Posted by dav1mo

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.

Lenny77 · #14 (**permalink**) 03-03-10, 16:24

Quote:

Originally Posted by n_i

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

Lenny77 · #15 (**permalink**) 03-03-10, 16:28

Quote:

Originally Posted by dvdaddict32

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

Internet Services

Web Development

Digital Marketing

Consumer Tech