Aggregative sum of objects belonging to objects residing inside hierarchy structure

Question

My problem is similar in a way to this one, yet different enough in my understanding.

I have three tables:

1. Units ([UnitID] int, [UnitParentID] int)
2. Students ([StudentID] int, [UnitID] int)
3. Events ([EventID] int, [EventTypeID] int, [StudentID] int)

Students belong to units, units are stacked in a hierarchy (tree form - one parent per child), and each student can have events of different types.

I need to sum up the number of events of each type per user, then aggregate for all users in a unit, then aggregate through hierarchy until I reach the mother of all units.

The result should be something like this:

My tools are SQL Server 2008 and Report Builder 3. I put up a SQL fiddle with sample data for fun.

Answer 1

Use this query:

;WITH CTE(Id, ParentId, cLevel, Title, ord) AS (
    SELECT 
        u.UnitID, u.UnitParentID, 1, 
        CAST('Unit ' + CAST(ROW_NUMBER() OVER (ORDER BY u.UnitID) AS varchar(3)) AS varchar(max)),
        CAST(RIGHT('000' + CAST(ROW_NUMBER() OVER (ORDER BY u.UnitID) AS varchar(3)), 3) AS varchar(max))
    FROM 
        dbo.Units u
    WHERE 
        u.UnitParentID IS NULL
    UNION ALL
    SELECT
        u.UnitID, u.UnitParentID, c.cLevel + 1, 
        c.Title + '.' + CAST(ROW_NUMBER() OVER (PARTITION BY c.cLevel ORDER BY c.Id) AS varchar(3)),
        c.ord + RIGHT('000' + CAST(ROW_NUMBER() OVER (ORDER BY u.UnitID) AS varchar(3)), 3)
    FROM
        dbo.Units u
        JOIN
        CTE c ON c.Id = u.UnitParentID
    WHERE 
        u.UnitParentID IS NOT NULL
), Units AS (
SELECT 
    u.Id, u.ParentId, u.cLevel, u.Title, u.ord,
    SUM(CASE WHEN e.EventTypeId = 1 THEN 1 ELSE 0 END) AS EventA,
    SUM(CASE WHEN e.EventTypeId = 2 THEN 1 ELSE 0 END) AS EventB,
    SUM(CASE WHEN e.EventTypeId = 3 THEN 1 ELSE 0 END) AS EventC,
    SUM(CASE WHEN e.EventTypeId = 4 THEN 1 ELSE 0 END) AS EventD
FROM 
    CTE u
    LEFT JOIN
    dbo.Students s ON u.Id = s.UnitId
    LEFT JOIN
    dbo.[Events] e ON s.StudentId = e.StudentId
GROUP BY
    u.Id, u.ParentId, u.cLevel, u.Title, u.ord
), addStudents AS (
SELECT *
FROM Units
UNION ALL
SELECT 
    s.StudentId, u.Id, u.cLevel + 1,
    'Student ' + CAST(s.StudentId AS varchar(3)),
    u.ord + RIGHT('000' + CAST(s.StudentId AS varchar(3)), 0),
    SUM(CASE WHEN e.EventTypeId = 1 THEN 1 ELSE 0 END),
    SUM(CASE WHEN e.EventTypeId = 2 THEN 1 ELSE 0 END),
    SUM(CASE WHEN e.EventTypeId = 3 THEN 1 ELSE 0 END),
    SUM(CASE WHEN e.EventTypeId = 4 THEN 1 ELSE 0 END)
FROM Units u
    JOIN
    dbo.Students s ON u.Id = s.UnitId
    LEFT JOIN
    dbo.[Events] e ON s.StudentId = e.StudentId
GROUP BY
    s.StudentID, u.ID, u.cLevel, u.ord
)
SELECT --TOP(10)
    REPLICATE(' ', cLevel) + Title As Title, 
    EventA, EventB, EventC, EventD
FROM
    addStudents 
ORDER BY
    ord

For this:

Title            | EventA | EventB  | EventC | EventD
-----------------+--------+---------+--------+--------
 Unit 1          | 0      | 1       | 0      | 0
  Student 6      | 0      | 1       | 0      | 0
  Unit 1.1       | 0      | 0       | 0      | 1
   Student 21    | 0      | 0       | 0      | 1
   Student 33    | 0      | 0       | 0      | 0
   Unit 1.1.1    | 0      | 0       | 0      | 0
    Student 23   | 0      | 0       | 0      | 0
    Unit 1.1.1.1 | 3      | 2       | 3      | 0
     Student 10  | 0      | 0       | 0      | 0
     Student 17  | 1      | 0       | 0      | 0
...

SQL Fiddle Demo

Answer 2

Do you need also the hierarchy to be sorted / visualized? At least this will calculate the sums, but the order of the data is pretty random :)

;with CTE as (
  select S.StudentId as UnitID, S.UnitId as UnitParentID,
    S.StudentID, 'Student' as Type
  from Students S
union all
  select U.UnitId, U.UnitParentId, 
    CTE.StudentId as StudentID, 'Unit   ' as Type
  from
    Units U
    join CTE
      on U.UnitId = CTE.UnitParentId
)
select C.Type + ' ' + convert(varchar, C.UnitId), 
  sum(case when EventTypeId = 1 then 1 else 0 end) as E1,
  sum(case when EventTypeId = 2 then 1 else 0 end) as E2,
  sum(case when EventTypeId = 3 then 1 else 0 end) as E3,
  sum(case when EventTypeId = 4 then 1 else 0 end) as E4
from 
  CTE C
  left outer join events E on C.StudentId = E.StudentId
group by
  C.Type, C.UnitId

SQL Fiddle

If you need also the hierarchy to be in order, you'll probably have add few extra CTEs to get the numbering from top down with something like @shA.t did. This gathers the hierarchy separately for each student, so it's not really possible to add the level numbers in a simple way.