下面的查询用于查找特定部门的员工人数。这很管用-

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

输出看起来像-

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

现在我要在分区和地区级别计算同一查询中的和。i、 e.同一查询中每个地区和每个部门的总人数（一月列）。这可能吗？
输出应该像-

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

因此，从上面的输出-部门总和应该是一个部门（ca部门和美国部门）所有员工总数的总和，而地区总和应该是鹿区的-1+0=1，纽昆区101+3=104，就像这样。