CODEWITHSUNDEEP
sql-servert-sql
Mujahid
Mujahid

Reputation: 33

sum up count of row or sum of data in hierarchy

DECLARE @orderTable TABLE
(
    orderNO INT IDENTITY(1,1),
    Qty NUMERIC(10,3),
    agentId int,
    parentid INT
)

INSERT INTO @orderTable (Qty, agentId, parentid )
VALUES (9, 1, 0),
       (2, 2, 1),
       (3, 3, 1)

SELECT * FROM @orderTable

orderNO Qty     agentId parentid
1       9.000   1       0
2       2.000   2       1
3       3.000   3       1

In the above table,I want to sum up the quantity based on the parent.And sum should have the parent order quantity also.

want to achieve this in a single query without using union.

Output:

parentId qty 
1        14

Upvotes: 0

Views: 69

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

This query:

SELECT o.agentId as parentId,  t.childrenSum + SUM(Qty) OVER() AS qty
FROM @orderTable AS o
OUTER APPLY (
   SELECT SUM(Qty) AS childrenSum
   FROM @orderTable
   WHERE parentid = o.agentId
) t
WHERE o.parentid = 0

produces this output:

parentId    qty
----------------
1           14.000

WHERE clause:

WHERE o.parentid = 0

presumably selects all parent records, whereas the OUTER APPLY sub-query calculates the quantity sum for all children of each of these parents.

Upvotes: 1

Related Questions