Questions on Unions Queries 1

[txdave35]

Hey guys,

I wrote a query to retrieve the number of residents per state.

SELECT          COUNT(DISTINCT   A.RECIP_SSN_NBR)        AS "NUMBER OF RETIREES"                      
                   ,C.STATE_ID_CD                   
             FROM DSNP.PR01_T_RECIP_SYS A                                        
                 ,DSNP.PR01_T_ANTY_PYMT B                                        
                 ,DSNP.PR01_T_MBR_CITY  C                                        
                 ,DSNP.PR01_T_CTY       D                                        
            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.CTY_ID_CD     = D.CTY_ID_CD                                  
              AND A.RECIP_TYPE_CD = '10'    
              AND B.ANTY_PYMT_DT >= '2009-05-01'                            
                                       
              AND B.ANTY_PYMT_STAT_CD = 'A'                                      
              AND C.MBR_ADDR_SEQ_NBR  = 1
              AND C.STATE_ID_CD <> 'AR'
                          
              
              GROUP BY                                                             
              C.STATE_ID_CD
             
                                                             
           UNION ALL                                                            
                                     
          SELECT COUNT (DISTINCT A.RECIP_SSN_NBR)        AS RECIP_SSN_NBR                       
           ,C.STATE_ID_CD                  
           FROM DSNP.PR01_T_RECIP_SYS  A                                       
          ,DSNP.PR01_T_ANTY_PYMT  B                                       
          ,DSNP.PR01_T_RECIP_CITY C                                       
          ,DSNP.PR01_T_CTY        D                                       
          WHERE A.RECIP_SSN_NBR = B.RECIP_SSN_NBR                              
          AND A.BENEF_SEQ_NBR = B.BENEF_SEQ_NBR                              
          AND A.RECIP_SSN_NBR = C.RECIP_SSN_NBR     
          AND C.CTY_ID_CD     = D.CTY_ID_CD                                  
          AND A.RECIP_TYPE_CD IN ('20', '30', '40')                          
          AND B.ANTY_PYMT_DT  >= '2009-05-01'                         
          AND B.ANTY_PYMT_STAT_CD = 'A'                                      
          AND C.RECIP_ADDR_SEQ_NBR  = 1
          AND C.STATE_ID_CD <> 'AR' 
                   
                                             
         GROUP BY                                                             
         C.STATE_ID_CD

The query runs without errors, and everything looked fine until I manually sorted the states. I am getting two records for each state.


I discovered that the Union query creates two counts per state. I want the two counts to be combined together per state.

Ex. WI - 2
WI - 4

should display as WI 6

Also, how can I tell SQL to order this query by state?

 

[txdave35]

I think I answered my own question. I just tested by using a sub-query, and it appears to work.

SELECT          COUNT(DISTINCT  RECIP_SSN_NBR)        AS "NUMBER OF RETIREES",
                A.state_id_cd
FROM (SELECT   A.RECIP_SSN_NBR                    
                   ,C.STATE_ID_CD                   
             FROM DSNP.PR01_T_RECIP_SYS A                                        
                 ,DSNP.PR01_T_ANTY_PYMT B                                        
                 ,DSNP.PR01_T_MBR_CITY  C                                        
                 ,DSNP.PR01_T_CTY       D                                        
            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.CTY_ID_CD     = D.CTY_ID_CD                                  
              AND A.RECIP_TYPE_CD = '10'    
              AND B.ANTY_PYMT_DT >= '2009-05-01'                            
                                       
              AND B.ANTY_PYMT_STAT_CD = 'A'                                      
              AND C.MBR_ADDR_SEQ_NBR  = 1
              AND C.STATE_ID_CD <> 'AR'
                          
              
              
             
                                                             
           UNION ALL                                                            
                                     
          SELECT A.RECIP_SSN_NBR        AS RECIP_SSN_NBR                       
           ,C.STATE_ID_CD                  
           FROM DSNP.PR01_T_RECIP_SYS  A                                       
          ,DSNP.PR01_T_ANTY_PYMT  B                                       
          ,DSNP.PR01_T_RECIP_CITY C                                       
          ,DSNP.PR01_T_CTY        D                                       
          WHERE A.RECIP_SSN_NBR = B.RECIP_SSN_NBR                              
          AND A.BENEF_SEQ_NBR = B.BENEF_SEQ_NBR                              
          AND A.RECIP_SSN_NBR = C.RECIP_SSN_NBR     
          AND C.CTY_ID_CD     = D.CTY_ID_CD                                  
          AND A.RECIP_TYPE_CD IN ('20', '30', '40')                          
          AND B.ANTY_PYMT_DT  >= '2009-05-01'                         
          AND B.ANTY_PYMT_STAT_CD = 'A'                                      
          AND C.RECIP_ADDR_SEQ_NBR  = 1
          AND C.STATE_ID_CD <> 'AR' 
                   
         ) AS A
         
         group by A.state_id_cd

 

[markros]

select count([Number of Retirees]) as TotalCnt, State_ID_CD from (

SELECT COUNT(DISTINCT A.RECIP_SSN_NBR) AS "NUMBER OF RETIREES"
,C.STATE_ID_CD
FROM DSNP.PR01_T_RECIP_SYS A
,DSNP.PR01_T_ANTY_PYMT B
,DSNP.PR01_T_MBR_CITY C
,DSNP.PR01_T_CTY D
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.CTY_ID_CD = D.CTY_ID_CD
AND A.RECIP_TYPE_CD = '10'
AND B.ANTY_PYMT_DT >= '2009-05-01'

AND B.ANTY_PYMT_STAT_CD = 'A'
AND C.MBR_ADDR_SEQ_NBR = 1
AND C.STATE_ID_CD <> 'AR'


GROUP BY
C.STATE_ID_CD


UNION ALL

SELECT COUNT (DISTINCT A.RECIP_SSN_NBR)
,C.STATE_ID_CD
FROM DSNP.PR01_T_RECIP_SYS A
,DSNP.PR01_T_ANTY_PYMT B
,DSNP.PR01_T_RECIP_CITY C
,DSNP.PR01_T_CTY D
WHERE A.RECIP_SSN_NBR = B.RECIP_SSN_NBR
AND A.BENEF_SEQ_NBR = B.BENEF_SEQ_NBR
AND A.RECIP_SSN_NBR = C.RECIP_SSN_NBR
AND C.CTY_ID_CD = D.CTY_ID_CD
AND A.RECIP_TYPE_CD IN ('20', '30', '40')
AND B.ANTY_PYMT_DT >= '2009-05-01'
AND B.ANTY_PYMT_STAT_CD = 'A'
AND C.RECIP_ADDR_SEQ_NBR = 1
AND C.STATE_ID_CD <> 'AR'


GROUP BY
C.STATE_ID_CD) AllInfo
group by State_ID_CD order by State_ID_CD

Using the original UNION select as derived table

 

[markros]

Great minds think alike

 

[txdave35]

Lol, thanks Markros. I really appreciate all the help. Derived tables and sub-queries are just new concepts that confuse me easily, but I think my comfort level is rising!

 

[MDXer]

If you are on SQL 2005 or 2008 try using a CTE I have found them to be easier to work with, since you reference the CTE as you would a table.