Two level grouping in sql query

Question

I have three tables that will be used in the query:

Departments:
DepartmentID - PK
DepartmentName

Earnings:
EarningID - PK
PersonID
EarningValue

People:
PersonID - PK
DepartmentID
Surname
Name

I need to make a query that will return 4 columns:
- DepartmentName
- Surname of Person
- Sum of all Earnings of Person
- Sum of all Earnings of Department

I was reading about ROLLUP, CUBE, and GROUPING SETS clauses, but i dont have idea how to use it here. An important thing is that i cannot use OVER (PARTITION

Answer 1

I think something like this would do the trick. (Assuming SQL Server)

Basically, you sum them up separately, and then join them together after that.

SELECT 
    DepartmentName,
    PersonEarningValue,
    Surname,
    PersonEarningValue
FROM
    (SELECT
        Departments.DepartmentID,
        Departments.DepartmentName,
        SUM(Earnings.EarningValue) as DeparmentEarningValue
    FROM Departments
    JOIN People   ON Departments.DepartmentID = People.DepartmentID
    JOIN Earnings ON People.PersonID = Earnings.PersonID
    GROUP BY Departments.DepartmentID, Departments.DepartmentName) as DepartmentSums
LEFT JOIN
    (SELECT
        DepartmentID,
        Surname,
        SUM(EarningValue) as PersonEarningValue
    FROM People   ON Departments.DepartmentID = People.DepartmentID
    JOIN Earnings ON People.PersonID = Earnings.PersonID
    GROUP BY DepartmentID, Surname) as PeopleSums
ON DepartmentSums.DepartmentID = PeopleSums.DepartmentID

Answer 2

I was trying with posted solution and with many my ideas. What i have is four columns with correct sums, but i have ID of department and ID of person, insted of the department name and surname. How i can change the query to replace those two columns?

SELECT DepartmentID, PersonID, SUM(EarningValue), PersonSum FROM People
JOIN Earnings USING(PersonID)
JOIN
(
SELECT DepartmentID, SUM(EarningValue) AS DepartmentSum FROM People
JOIN Earnings USING(PersonID)
GROUP BY DepartmentID
) USING (DepartmentID)
GROUP BY DepartmentID, PersonID, DepartmentSum
ORDER BY DepartmentID

Answer 3

I work on DB2, but I took a quick look at an Oracle manual, and it looks pretty close to DB2 in this respect. This query should get you pretty close to the finish line, although it exposes 3 result columns, where Sum_Earnings represents either a department total, person total, or grand total for all departments, depending on the sum record type (the GROUPING function is used to determine which level of total row you're dealing with). With minor tweaking you should be able to turn this into exposing 4 result columns if you really need it (hint: grouping function).

select   decode( grouping( D.DepartmentName ), 1, 'All Departments', D.DepartmentName ) 
            as Department
        ,decode( grouping( P.Surname ), 1, 'All Persons', P.Surname ) as Person_Surname
        ,sum( E.EarningValue ) as Sum_Earnings 

from     Departments D
join     People      P
    on   P.DepartmentID = D.DepartmentID
join     Earnings    E
    on   E.PersonID = P.PersonID

group by rollup( D.DepartmentName, P.Surname )
order by grouping( D.DepartmentName ), Department, grouping( P.Surname ), Person_Surname

Because CUBE, ROLLUP, and GROUPING SETS can perform these tasks with a much smaller volume of code, they are really the way to go as there is less chance of making a coding mistake (once you wrap you head around them), and if you're producing a summary report like this, by allowing the database to perform all the summation work, you're likely to get much better performance (as opposed to reading detail rows into a program which calculates totals by churning detail rows - ugh).

It usually takes me a couple tries to get the row ordering and total labeling correct on these types of queries as I don't do them often (rarely work on reporting in recent years), so hopefully I didn't hose that up.