How to Combine Queries into One Main Query?

[lrdave36]

Hey guys,

I am writing a query that performs multiple SUMS, and want to combine the summations all into one big query.

I can't figure out the syntax to do this. I tried UNION this way:

SELECT   sum(A.ANTY_PYMT_tot_AMT) AS "TOTAL TAPERS DEPOSIT AMOUNT" 
         FROM     DSNP.PR01_T_ANTY_PYMT A, 
         DSNP.PR01_T_RECIP_SYS B 
WHERE    A.RECIP_SSN_NBR=B.RECIP_SSN_NBR 
AND      A.BENEF_SEQ_NBR=B.BENEF_SEQ_NBR 
and      b.agty_sys_cd = 'TAPERS'
AND      A.ANTY_PYMT_DT BETWEEN '2010-02-01' AND '2010-02-28' 
AND      A.ANTY_PYMT_STAT_CD = 'A'
UNION
SELECT   sum(A.ANTY_PYMT_tot_AMT) AS "TOTAL TAJRS DEPOSIT AMOUNT" 
        FROM     DSNP.PR01_T_ANTY_PYMT A, 
         DSNP.PR01_T_RECIP_SYS B 
WHERE    A.RECIP_SSN_NBR=B.RECIP_SSN_NBR 
AND      A.BENEF_SEQ_NBR=B.BENEF_SEQ_NBR 
and      b.agty_sys_cd = 'TAJRS'
AND      A.ANTY_PYMT_DT BETWEEN '2010-02-01' AND '2010-02-28' 
AND      A.ANTY_PYMT_STAT_CD = 'A'

It kinda works, but it lumps everything under one column as "TOTAL TAPERS DEPOSIT AMOUNT"

Is there any way to do this?

 

[gmmastros]

Try this:

SELECT   sum(Case When b.agty_sys_cd = 'TAPERS' Then A.ANTY_PYMT_tot_AMT End) AS [TOTAL TAPERS DEPOSIT AMOUNT],
         sum(Case When b.agty_sys_cd = 'TAJRS'  Then A.ANTY_PYMT_tot_AMT End) AS [TOTAL TAJRS DEPOSIT AMOUNT]
         FROM     DSNP.PR01_T_ANTY_PYMT A,
         DSNP.PR01_T_RECIP_SYS B
WHERE    A.RECIP_SSN_NBR=B.RECIP_SSN_NBR
AND      A.BENEF_SEQ_NBR=B.BENEF_SEQ_NBR
AND      A.ANTY_PYMT_DT BETWEEN '2010-02-01' AND '2010-02-28'
AND      A.ANTY_PYMT_STAT_CD = 'A'

Notice how I left the common parts alone. The only difference between the 2 queries was the where clause criteria on agty_sys_cd. So... I moved that in to the SUM part with a Case/When statement.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom