Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need Help Combining Totals and Queries

Status
Not open for further replies.
lrdave36

lrdave36

Technical User
Jan 6, 2010
77
US
Hey guys,


I have two queries that use derive tables, and I need to combine totals together to get a grand total. I can do this manually on the spreadsheet, but it is so time consuming.


First I have this query for payouts:


Code: 
SELECT A.CTY_ID_NM                      AS COUNTY_NAME
        ,COUNT(*)                         AS COUNT
       ,SUM(A.TOTAL_AMOUNT)  as TOTAL_PAYOUT
    
    
    FROM (SELECT E.CTY_ID_NM              AS CTY_ID_NM
                ,A.RECIP_TYPE_CD          AS RECIP_TYPE_CD
                ,A.RECIP_SSN_NBR          AS RECIP_SSN_NBR
                ,SUM(B.GROSS_AMT) AS TOTAL_AMOUNT
            FROM DSNP.PR01_T_RECIP_SYS    A
                ,DSNP.PR01_T_PAYOUT    B
                ,DSNP.PR01_T_MBR_CITY     C
                ,DSNP.PR01_T_ZIP          D
                ,DSNP.PR01_T_CTY          E
           WHERE A.RECIP_SSN_NBR      = B.RECIP_SSN_NBR
             AND A.BENEF_SEQ_NBR      = B.BENEF_SEQ_NBR
             AND A.RECIP_SSN_NBR      = C.MBR_SSN_NBR
             AND C.STATE_ID_CD        = D.STATE_ID_CD
             AND C.POST_ZIP_CD        = D.POST_ZIP_CD
             AND D.CTY_ID_CD          = E.CTY_ID_CD
             AND C.MBR_ADDR_SEQ_NBR   = 1
             AND B.PYMT_TYPE in ('D', 'P')
             AND B.PYMT_DATE BETWEEN '2009-07-01'
                                    AND '2010-06-30'
                                    
           GROUP BY
                 E.CTY_ID_NM
                ,A.RECIP_TYPE_CD
                ,A.RECIP_SSN_NBR  ) as A

GROUP BY A.CTY_ID_NM


I have a similiar second query to find total payments:

Code: 
SELECT A.CTY_ID_NM                      AS COUNTY_NAME
        ,COUNT(*)                         AS COUNT
        ,SUM(A.ANTY_PYMT_TOT_AMT)         AS TOTAL_AMOUNT
    FROM (SELECT E.CTY_ID_NM              AS CTY_ID_NM
                ,A.RECIP_TYPE_CD          AS RECIP_TYPE_CD
                ,A.RECIP_SSN_NBR          AS RECIP_SSN_NBR
                ,SUM(B.ANTY_PYMT_TOT_AMT) AS ANTY_PYMT_TOT_AMT
            FROM DSNP.PR01_T_RECIP_SYS    A
                ,DSNP.PR01_T_ANTY_PYMT    B
                ,DSNP.PR01_T_MBR_CITY     C
                ,DSNP.PR01_T_ZIP          D
                ,DSNP.PR01_T_CTY          E
           WHERE A.RECIP_SSN_NBR      = B.RECIP_SSN_NBR
             AND A.BENEF_SEQ_NBR      = B.BENEF_SEQ_NBR
             AND A.RECIP_SSN_NBR      = C.MBR_SSN_NBR
             AND C.STATE_ID_CD        = D.STATE_ID_CD
             AND C.POST_ZIP_CD        = D.POST_ZIP_CD
             AND D.CTY_ID_CD          = E.CTY_ID_CD
             AND B.ANTY_PYMT_STAT_CD  = 'A'
             AND C.MBR_ADDR_SEQ_NBR   = 1
             AND B.ANTY_PYMT_DT BETWEEN '2009-07-01'
                                    AND '2010-06-30'


            GROUP BY
                 E.CTY_ID_NM
                ,A.RECIP_TYPE_CD
                ,A.RECIP_SSN_NBR  ) as A

GROUP BY A.CTY_ID_NM


I need to combine these queries somehow, and end up with one total field that is TOTAL_PAYOUT + TOTAL_AMOUNT for each county.


Can anyone help?
 
Sort by date Sort by votes
Can't you just do the SUM in a single query?

Code: 
SELECT A.CTY_ID_NM                      AS COUNTY_NAME
        ,COUNT(*)                         AS COUNT
       [red],SUM(A.TOTAL_AMOUNT)  as TOTAL_PAYOUT
       ,SUM(A.ANTY_PYMT_TOT_AMT) AS ANTY_PYMT_TOT_AMT[/red]
    
    
    FROM (SELECT E.CTY_ID_NM              AS CTY_ID_NM
                ,A.RECIP_TYPE_CD          AS RECIP_TYPE_CD
                ,A.RECIP_SSN_NBR          AS RECIP_SSN_NBR
                [red],SUM(B.GROSS_AMT) AS TOTAL_AMOUNT
                ,SUM(B.ANTY_PYMT_TOT_AMT) AS ANTY_PYMT_TOT_AMT[/red]
            FROM DSNP.PR01_T_RECIP_SYS    A
                ,DSNP.PR01_T_PAYOUT    B
                ,DSNP.PR01_T_MBR_CITY     C
                ,DSNP.PR01_T_ZIP          D
                ,DSNP.PR01_T_CTY          E
           WHERE A.RECIP_SSN_NBR      = B.RECIP_SSN_NBR
             AND A.BENEF_SEQ_NBR      = B.BENEF_SEQ_NBR
             AND A.RECIP_SSN_NBR      = C.MBR_SSN_NBR
             AND C.STATE_ID_CD        = D.STATE_ID_CD
             AND C.POST_ZIP_CD        = D.POST_ZIP_CD
             AND D.CTY_ID_CD          = E.CTY_ID_CD
             AND C.MBR_ADDR_SEQ_NBR   = 1
             AND B.PYMT_TYPE in ('D', 'P')
             AND B.PYMT_DATE BETWEEN '2009-07-01'
                                    AND '2010-06-30'
                                    
           GROUP BY
                 E.CTY_ID_NM
                ,A.RECIP_TYPE_CD
                ,A.RECIP_SSN_NBR  ) as A

GROUP BY A.CTY_ID_NM
 
Upvote 0 Downvote
This wouldn't work because Total_Payout and Total_Amount have different selection criteria. Also, I need one sum in my main Select, not two.

 
Upvote 0 Downvote
Depending on your SQL Server version, you could buid a CTE for each and join them by County.
Code: 
WITH One_CTE
(
    SELECT
        County
        ,SomeStuff
    FROM
        A_Table
)
,Two_CTE
(
    SELECT
        County
        ,SomeOtherStuff
    FROM
        B_Table
)
SELECT
    a.County
    a.SomeStuff + b.SomeOtherStuff
FROM
    One_CTE a
JOIN
    Two_CTE b ON a.County = b.County
Please note the above was written without checking the syntax in any way :)

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett
 
Upvote 0 Downvote
Can anyone tell me why this query fails? It tells me A.recip_ssn_nbr is invalid


Code: 
SELECT A.COUNTY_NAME, A.TOTAL_PAYOUT

FROM 


(SELECT A.CTY_ID_NM                      AS COUNTY_NAME
        ,COUNT(*)                         AS COUNT
       ,SUM(A.TOTAL_AMOUNT)  as TOTAL_PAYOUT
    
    
    FROM (SELECT E.CTY_ID_NM              AS CTY_ID_NM
                ,A.RECIP_TYPE_CD          AS RECIP_TYPE_CD
                ,A.RECIP_SSN_NBR          AS RECIP_SSN_NBR
                ,SUM(B.GROSS_AMT) AS TOTAL_AMOUNT
            FROM DSNP.PR01_T_RECIP_SYS    A
                ,DSNP.PR01_T_PAYOUT    B
                ,DSNP.PR01_T_MBR_CITY     C
                ,DSNP.PR01_T_ZIP          D
                ,DSNP.PR01_T_CTY          E
           WHERE A.RECIP_SSN_NBR      = B.RECIP_SSN_NBR
             AND A.BENEF_SEQ_NBR      = B.BENEF_SEQ_NBR
             AND A.RECIP_SSN_NBR      = C.MBR_SSN_NBR
             AND C.STATE_ID_CD        = D.STATE_ID_CD
             AND C.POST_ZIP_CD        = D.POST_ZIP_CD
             AND D.CTY_ID_CD          = E.CTY_ID_CD
             AND C.STATE_ID_CD        = 'AR'
             AND C.MBR_ADDR_SEQ_NBR   = 1
             AND A.AGTY_SYS_CD = 'ASPRS'
             AND B.PYMT_TYPE in ('D', 'P')
             AND B.PYMT_DATE BETWEEN '2009-07-01'
                                    AND '2010-06-30'
                                    
           GROUP BY
                 E.CTY_ID_NM
                ,A.RECIP_TYPE_CD
                ,A.RECIP_SSN_NBR  ) as A
                
                
                
   GROUP BY A.CTY_ID_NM ) as A,
   
   
   (SELECT B.CTY_ID_NM                      
        ,COUNT(*)                         AS COUNT
        ,SUM(B.ANTY_PYMT_TOT_AMT)         AS TOTAL_AMOUNT
    FROM (SELECT E.CTY_ID_NM              AS CTY_ID_NM
                ,A.RECIP_TYPE_CD          AS RECIP_TYPE_CD
                ,A.RECIP_SSN_NBR          AS RECIP_SSN_NBR
                ,SUM(B.ANTY_PYMT_TOT_AMT) AS ANTY_PYMT_TOT_AMT
            FROM DSNP.PR01_T_RECIP_SYS    A
                ,DSNP.PR01_T_ANTY_PYMT    B
                ,DSNP.PR01_T_MBR_CITY     C
                ,DSNP.PR01_T_ZIP          D
                ,DSNP.PR01_T_CTY          E
           WHERE A.RECIP_SSN_NBR      = B.RECIP_SSN_NBR
             AND A.BENEF_SEQ_NBR      = B.BENEF_SEQ_NBR
             AND A.RECIP_SSN_NBR      = C.MBR_SSN_NBR
             AND C.STATE_ID_CD        = D.STATE_ID_CD
             AND C.POST_ZIP_CD        = D.POST_ZIP_CD
             AND D.CTY_ID_CD          = E.CTY_ID_CD
             AND C.STATE_ID_CD        = 'AR'
             AND B.ANTY_PYMT_STAT_CD  = 'A'
             AND C.MBR_ADDR_SEQ_NBR   = 1
             AND A.AGTY_SYS_CD = 'ASPRS'
             AND B.ANTY_PYMT_DT BETWEEN '2009-07-01'
                                    AND '2010-06-30'
             GROUP BY E.CTY_ID_NM 
                      ,A.RECIP_TYPE_CD
                       ,A.RECIP_SSN_NBR                                    
                               
                                    
                                    ) as B
           
           GROUP BY B.CTY_ID_NM  ) as B
           
           
           where A.recip_ssn_nbr = B.recip_ssn_nbr


 
Upvote 0 Downvote
Simply because you don't select that field in your outer select.

If you have

select Fields from (select Col1, Total from (select other fields, InnerField) A) A
inner join
(select Col2, SomeTotal from (select ... from B) B) B
on A.Col1 = B.Col2

you can not reference a.InnerField in the join condition.


PluralSight Learning Library
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

f0rg3tfu1
  • Locked
  • Question
PLSQL Package Help
Replies
0
Views
379
f0rg3tfu1
f0rg3tfu1
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
875
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
Gaps and islands
Replies
8
Views
316
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
Derive staus date time by aligning start and end datetime
Replies
7
Views
403
Andrzejek
Andrzejek
Billz66
  • Locked
  • Question
need to udate table entry with number containing a *
Replies
11
Views
272
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top