SQL query to get sum of headcount according to column grouping

The below query is used to find out the headcount of employees in a particular department. This is working fine-

 SELECT distinct --LVL2_ORG,
            division, 
           district, 
           department, 
           Count(DISTINCT person_number) 
             over ( 
               PARTITION BY department) AS dep_empl_count 
    FROM   flattened_tree, 
           hr_org_unit_classifications_f houcf, 
           hr_all_organization_units_f haouf, 
           hr_organization_units_f_tl hauft, 
           per_all_assignments_m paam, 
           per_all_people_f papf 
    WHERE  haouf.organization_id = flattened_tree.department_id 
           AND haouf.organization_id = houcf.organization_id 
           AND haouf.organization_id = hauft.organization_id 
           AND haouf.effective_start_date BETWEEN 
               houcf.effective_start_date AND houcf.effective_end_date 
           AND hauft.LANGUAGE = 'US' 
           AND hauft.effective_start_date = haouf.effective_start_date 
           AND hauft.effective_end_date = haouf.effective_end_date 
           AND houcf.classification_code = 'DEPARTMENT' 
           AND Trunc(SYSDATE) BETWEEN hauft.effective_start_date AND 
                                      hauft.effective_end_date 
           AND hauft.organization_id = paam.organization_id 
           AND paam.person_id = papf.person_id 
           AND paam.primary_assignment_flag = 'Y' 
           AND paam.assignment_type = 'E' 
           AND paam.assignment_status_type IN ( 'ACTIVE', 'SUSPENDED' ) 
           AND paam.effective_latest_change = 'Y' 
           AND trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
AND trunc(sysdate) between papf.effective_start_date and papf.effective_end_date

ORDER  BY division,district,department

The output looks like –

DIVISION            DISTRICT        DEPARTMENT              JAN
CA Division         Grande          XYZ                     0
CA Division         Deer            Deer Tubing             1
CA Division         Deer            XYZ  Fracturing         0
CA Division         Fracturing      CAA  Fracturing         3

US Division         Neuquen         Support                 101
US Division         Neuquen         Cementing               3
US Division         Corporate       Fracturing              190

Now I want to calculate the sum in the same query at the division and district level. i.e. sum of headcounts(JAN Column) per district and per division in the same query. Is this possible ?

The output should look like –

DIVISION            DISTRICT        DEPARTMENT              JAN    SUM(DIVISION)    SUM(DISTRICT)
CA Division         Grande          XYZ                     0        4               0
CA Division         Deer            Deer Tubing             1        4               1
CA Division         Deer            XYZ  Fracturing         0        4               1
CA Division         Fracturing      CAA  Fracturing         3        4               3

US Division         Neuquen         Support                 101      294            104        
US Division         Neuquen         Cementing               3        294            104
US Division         Corporate       Fracturing              190      294            190

so from above output- sum of division should be sum of all headcounts in one division (CA Division and US Division) and sum of district should be for Deer district – 1+0=1 for Neuquen district 101+3=104 like that.

Answer

Instead of 1 measure:

   Count(DISTINCT person_number) 
     over ( 
       PARTITION BY department) AS dep_empl_count

Use 3 measures:

   Count(DISTINCT person_number) over (PARTITION BY department) AS dep_empl_count, 
   Count(DISTINCT person_number) over (PARTITION BY division) AS div_empl_count, 
   Count(DISTINCT person_number) over (PARTITION BY district) AS dis_empl_count

Advertisement

Answer