Problems Creating a Query with Multiple Counts

[VAST39]

I am trying to write a query that will tell me the number of employees in multiple categories. It's been awhile since I wrote a query like this, and I just can't remember how to output multiple counts with column names.

I first tried a union

SELECT SUM(A.TOTAL) AS TOTAL_SCHOOL


FROM 


(SELECT   count(distinct a.mbr_ssn_nbr) as TOTAL
        
         
         
         
FROM     DSNP.PR01_T_SYSTEM A,
         DSNP.PR01_T_HISTORY B
WHERE    A.MBR_SSN_NBR=B.MBR_SSN_NBR
AND      A.MBR_STAT_CD IN ('A', '1')
AND      B.mbr_hist_svc_cr_dt = '2013-10-31'
and      b.hist_categ_cd = '10'




and left(B.AGTY_ID_CD, 1) = '3' ) AS A


UNION ALL


SELECT SUM(A.TOTAL) AS TOTAL_COUNTY


FROM 


(SELECT   count(distinct a.mbr_ssn_nbr) as TOTAL
        
         
         
         
FROM     DSNP.PR01_T_SYSTEM A,
         DSNP.PR01_T_HISTORY B
WHERE    A.MBR_SSN_NBR=B.MBR_SSN_NBR
AND      A.MBR_STAT_CD IN ('A', '1')
AND      B.mbr_hist_svc_cr_dt = '2013-10-31'
and      b.hist_categ_cd = '10'




and left(B.AGTY_ID_CD, 1) = '0' ) AS A

It does give me two counts, but there is no description. The output looks like this:


<no column name>

234
23567


I need the output to identify the count with the appropriate category.

Can anyone help?

 

[imex]

Try:

SELECT   
    count(distinct case when left(B.AGTY_ID_CD, 1) = '3' then a.mbr_ssn_nbr end) as TOTAL_SCHOOL,
    count(distinct case when left(B.AGTY_ID_CD, 1) = '0' then a.mbr_ssn_nbr end) as TOTAL_COUNTY
FROM     DSNP.PR01_T_SYSTEM A,
         DSNP.PR01_T_HISTORY B
WHERE    A.MBR_SSN_NBR=B.MBR_SSN_NBR
AND      A.MBR_STAT_CD IN ('A', '1')
AND      B.mbr_hist_svc_cr_dt = '2013-10-31'
and      b.hist_categ_cd = '10'

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[gmmastros]

I think you are making it more complicated than you need to (assuming I properly understand your question).

try this:

SELECT  [!]left(B.AGTY_ID_CD, 1) As Category,[/!]
	count(distinct a.mbr_ssn_nbr) as TOTAL
FROM    DSNP.PR01_T_SYSTEM A,
        DSNP.PR01_T_HISTORY B
WHERE   A.MBR_SSN_NBR=B.MBR_SSN_NBR
        AND A.MBR_STAT_CD IN ('A', '1')
        AND B.mbr_hist_svc_cr_dt = '2013-10-31'
        and b.hist_categ_cd = '10'
        and left(B.AGTY_ID_CD, 1) [!]IN ('0','3')[/!]
[!]GROUP BY left(B.AGTY_ID_CD, 1)[/!]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I did not see imex's suggestion before posting mine. His query should work perfectly well. The difference between his query and mine is that both counts will appear in the same row with his query. With mine, there will be 2 rows with 2 columns (a category column and a count column).



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom