Counting in SQL

[ghalewood]

Hi, I have this query which is listed below. The main sort is field E.VTARECLK and I have the report grouping these with page throws etc. I have been asked to provide a number of unique A.SERNUM within each group of E.VTARECLK. I can get the count of records in each group, but I have been unsuccessful in getting the number of unique serial numbers.

The report shows the people whose total salary is more than £1000 different between this month and last month so validity checks can be made.

Can anyone help please.

SELECT A.CTRY, D.XPCARDPT, D.A06FIRNAM, D.A07NOMUSE,                    
       A.SERNUM, K1CODRUB, C.STDESC, 0, K1MONSAL, ESDATSOR, E.VTARECLK  
                                                                        
FROM HRDDBA.HRPXRESLP02S A,                                             
     HRDDBA.HRDXDRC B,                                                  
     HRDDBA.HRDXDES C,                                                  
     HRDDBA.HRYXCURRINFO D,                                             
     HRDDBA.HRAOT_GBODB E                                               
                                                                        
WHERE A.CTRY = 'GB'                                                     
  AND B.MODEL = 'GBR'                                                   
  AND C.MODEL = 'GBR'                                                   
  AND D.CTRY = 'GB'                                                     
  AND A.K1CODRUB = B.CODE                                               
  AND A.K1CODRUB = C.CODE                                               
  AND E.DPTCOD = D.XPCARDPT                                             
  AND A.SERNUM = D.SERNUM                                               
  AND B.CQNETPAY = 'Y'                                                  
  AND C.CDLANG = 'K'                                                    
  AND C.DIR = 'DRC'                                                     
  AND INTEFLAG <> '1'                                                   

AND ESDATSOR <>'2999-12-31'                                            
AND A.SERNUM IN                                                        
                                                                       
 (   SELECT A.SERNUM                                                   
                                                                       
     FROM HRDDBA.HRPXRES02S A,                                         
          HRDDBA.HRPXRESLP02S B                                        
                                                                       
     WHERE A.CTRY = 'GB'                                               
       AND B.CTRY = 'GB'                                               
       AND A.SERNUM = B.SERNUM                                         

       AND A.K1CODRUB = '975'                                          
       AND B.K1CODRUB = '975'                                          
       AND A.INTEFLAG <> '1'                                           
       AND B.INTEFLAG <> '1'                                           
       AND (A.K1MONSAL - B.K1MONSAL) NOT BETWEEN -1000 AND 1000        
  )                                                                    
                                                                       
UNION ALL                                                              
                                                                       
SELECT A.CTRY, D.XPCARDPT, D.A06FIRNAM, D.A07NOMUSE,                       
       A.SERNUM,  K1CODRUB, C.STDESC, K1MONSAL, 0, ESDATSOR, E.VTARECLK    
                                                                           
FROM HRDDBA.HRPXRES02S A,                                                  
     HRDDBA.HRDXDRC B,                                                     
     HRDDBA.HRDXDES C,                                                     
     HRDDBA.HRYXCURRINFO D,                                                
     HRDDBA.HRAOT_GBODB E                                                  
                                                                           
WHERE A.CTRY = 'GB'                                                        
  AND B.MODEL = 'GBR'                                                      
  AND C.MODEL = 'GBR'                                                      
  AND D.CTRY = 'GB'                                                        
  AND A.K1CODRUB = B.CODE                                                  
  AND A.K1CODRUB = C.CODE                                                  
  AND E.DPTCOD = D.XPCARDPT                                                
  AND A.SERNUM  =  D.SERNUM                                                
  AND B.CQNETPAY = 'Y'                                                     
  AND C.CDLANG='K'                                                         
  AND C.DIR = 'DRC'                                                        
  AND INTEFLAG <> '1'                                                      
 --D A.SERNUM < '0001000'                                                  
 AND ESDATSOR <> '2999-12-31'                                              
  AND A.SERNUM IN                                                          
FROM HRDDBA.HRPXRES02S A,                                           
     HRDDBA.HRPXRESLP02S B                                          
                                                                    
     WHERE A.CTRY = 'GB'                                            
       AND B.CTRY = 'GB'                                            
       AND A.SERNUM = B.SERNUM                                      
--D A.SERNUM < '0001000'                                            
       AND A.K1CODRUB = '975'                                       
       AND B.K1CODRUB = '975'                                       
       AND A.INTEFLAG <> '1'                                        
       AND B.INTEFLAG <> '1'                                        
       AND (A.K1MONSAL - B.K1MONSAL) NOT BETWEEN -1000 AND 1000     
  )                                                                 
                                                                    
                                                                    
ORDER BY 11,1,2,5,6

 

[grofaty]

Hi,

Can you please simplify your sample SQL?

Grofaty

 

[ghalewood]

Hi Grofaty,

Not really, thats the actual SQL that I need the count adding to. If I simplify the SQL, it might invalidate any answer that I am given.

Thanks for looking though.

 

[blom0344]

Not sure how the displayed SQL relates to apply the counting you want, but with OLAP this would be something like:

Count(distinct A.SERNUM) over (partition by E.VTARECLK)

You may have to wrap your line-SQL with the UNION in a view first and then do the count over it.

Ties Blom
Information analyst
tbl@shimano-eu.com