Can You Perform Multiple Counts in One Query?

[txdave35]

Hey guys,

I am trying to create a query that will display as such:


Years of Service Count
________________ __________


1 2640

2 3344

3 2345


So in the above example, 2640 employees have one year of service, 3344 employees have 2 years of service.

Is there any way to have a single query perform multiple counts? My query here only counts the first year:

SELECT                                                                         
  B.MBR_TOT_SVC_YY_CT as "YEARS of SERVICE",
  count(B.MBR_SSN_NBR) as "NUMBER OF EMPLOYEES"                                                                 
                                                            
  FROM                                                                           
  dsnp.pr01_T_MBR_EMPR A,                                                        
  dsnp.pr01_T_MBR_SYS B                                                          
  WHERE                                                                          
  A.MBR_SSN_NBR = B.MBR_SSN_NBR AND                                              
  B.MBR_STAT_CD = '1'  AND                                                       
  A.MBR_F_EMP_DT = (SELECT MAX(D.MBR_F_EMP_DT)                                   
      FROM DSNP.PR01_T_MBR_EMPR D                                                
      WHERE                                                                      
      A.MBR_SSN_NBR = D.MBR_SSN_NBR) AND                                         
 A.AGTY_ID_CD > '00000' AND A.AGTY_ID_CD < '01000' AND                          
  A.AGTY_ID_CD <> '00499' AND A.AGTY_ID_CD <> '00500'  
  and B.MBR_TOT_SVC_YY_CT = 1                          

group by B.MBR_TOT_SVC_YY_CT

Thanks!

 

[txdave35]

Disregard, I'm an idiot, I figured it out. lol

 

[markros]

Yes, you just don't need to include condition on number of years of service, that's all.