www1986 - 1 year ago 85
SQL Question

# Calculate sum of values in tree(recursive query)

I have tree structure in table employees (id,name,parentid) and this table can be nested.employees is one-to-many relation to another table Sales with columns(id, employeeid, quantity). Each employee has Sales Quantity. I want to calculate sum of quantity for each employee along side with child employees. I wrote some code to be more clearly.

``````DECLARE @Employees TABLE(ID INT, Name NVARCHAR(100), ParentID INT);
DECLARE @Sales TABLE(ID INT, EmployeeID INT, Quantity INT);

INSERT INTO @Employees(ID, Name, ParentID)VALUES
(1,N'Employee1', NULL),
(2,N'Employee2', 1),
(3,N'Employee3', 2),
(4,N'Employee4', NULL),
(5,N'Employee5', 4),
(6, N'Employee6', 5)

INSERT INTO @Sales(ID, EmployeeID, Quantity)VALUES
(1,1,4),
(2,1,2),
(3,2,3),
(4,3,2),
(5,3,7),
(6,5,8),
(7,5,3),
(8,6,2)
``````

I joined this Tables and looks likes this:

Here is my query

``````;WITH cte
AS
(
SELECT e.ID, e.Name, e.ParentID FROM @Employees e
WHERE e.ParentID IS NULL
UNION ALL
SELECT  e.ID, e.Name, e.ParentID  FROM @Employees e
INNER JOIN cte c ON c.ID = e.ParentID
)
SELECT
c.ID
,c.Name
,c.ParentID
,ISNULL(SUM(s.Quantity), 0) AS ParentSumSales
,ISNULL(LEAD(SUM(s.Quantity)) OVER(ORDER BY c.ID), 0) AS ChildSumSales
FROM cte c
LEFT JOIN @Sales s ON s.EmployeeID = c.ID
GROUP BY c.ID, c.Name, c.ParentID
``````

query returns this result:

But this is not correct, I want to get data like this:

``````ID     Name        ParentSumSales    ChildSumSales
---    ---------   -------------     -------------
1      Employee1   6                 12
2      Employee2   3                 9
3      Employee3   9                 0
4      Employee4   0                 13
5      Employee5   11                2
6      Employee6   2                 0
``````

Answer Source

Your attempt using `LEAD` will not work as it's not summing all previous levels and it IDs must be sequential.

Explode the full hierachy for each employee first such that each employe is included once per hierarchy level:

``````;WITH cte
AS
(
SELECT e.ID, e.Name, e.ID as sub_ID
FROM @Employees e
-- no WHERE-condition to get all employees
UNION ALL
SELECT
c.ID, c.Name -- keep the initial employee
,e.ID as sub_ID
FROM @Employees e
INNER JOIN cte c ON c.sub_ID = e.ParentID
)

SELECT
c.ID
,c.Name
-- parent level
,sum(case when c.id =  s.EmployeeID then s.Quantity else 0 end) AS ParentSumSales
-- child level
,sum(case when c.id <> s.EmployeeID then s.Quantity else 0 end) AS ChildSumSales
FROM cte c
LEFT JOIN @Sales as s
ON s.EmployeeID = c.sub_ID
group by c.Name, c.id
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download