Sum of all child and parent in hierarchical data

Question

5.00/5 (1 vote)

See more:

hi I have 2 table for sale with below fields:

saleDetail (id, commodityID, count, price, ...)
commodity (id, parentID, name)

commodity table is hierarchical or recursive.

There are my test data

commodity

id-- parentID-- name
1-- null-- foods
2-- 1-- fruits
3-- 2-- apple
4-- 2-- orange
5-- 2-- melon
6-- 1-- sea foods
7-- 6-- salmon
8-- 6-- shrimp

saleDetail

commodityID-- count-- price
3-- 1-- 100
4-- 1-- 150
5-- 1-- 200
7-- 1-- 110
8-- 1-- 180
4-- 1-- 160

I want a report like below:

name-- total
foods-- 900 (sum of all records)
fruits-- 610 (sum of 3, 4, 5)
apple-- 100 (sum of 3)
orange-- 310 (sum of 4)
melon-- 200 (sum of 5)
sea foods-- 290 (sum of 7, 8)
salmon-- 110 (sum of 7)
shrimp-- 180 (sum of 8)

What I have tried:

this is my sql query but not a correct answer:

WITH tree AS
(
--initialization
SELECT id, parentid, name
FROM commodity
WHERE parentID is null
UNION ALL
--recursive execution
SELECT e.id, e.parentid, e.name
FROM commodity e INNER JOIN tree m
ON e.parentID = m.id
)
--SELECT * FROM tree
select name, sum(count*price) as total from tree RIGHT OUTER JOIN
saleDetail ON tree.id = saleDetail.commodityID
group by name

result; that total of parents not calculated

name-- total
apple-- 100
melon-- 200
orange-- 310
salmon-- 110
shrimp-- 180

Posted 30-Sep-18 5:52am

maysamfth

Updated 30-Sep-18 8:31am

v3

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2018-09-30T08:32:00

Check this:

SQL

DECLARE @commodity TABLE(id INT IDENTITY(1,1), parentID INT, [name] VARCHAR(30))
INSERT INTO @commodity(parentID, [name])
VALUES(null, 'foods'),
(1, 'fruits'),
(2, 'apple'),
(2, 'orange'),
(2, 'melon'),
(1, 'sea foods'),
(6, 'salmon'),
(6, 'shrimp')


DECLARE @saleDetail TABLE(commodityID INT, [count] INT, price INT)
INSERT INTO @saleDetail (commodityID, [count], price)
VALUES(3, 1, 100),
(4, 1, 150),
(5, 1, 200),
(7, 1, 110),
(8, 1, 180),
(4, 1, 160)

;WITH tree AS
(
	--initial part
	SELECT c.ID, c.parentID, c.[name], sd.[count] AS countOfItems, sd.price
	FROM @saleDetail AS sd INNER JOIN @commodity AS c ON sd.commodityID = c.id 
	UNION ALL
	--recursive part
	SELECT c.ID, c.parentID, c.[name], t.countOfItems, t.price 
	FROM @commodity AS c INNER JOIN tree AS t ON c.id = t.parentID 
)
SELECT t.[name], SUM(t.countOfItems * t.price) AS total
FROM tree AS T
GROUP BY t.[name]

Result:

name	total
apple	100
foods	900
fruits	610
melon	200
orange	310
salmon	110
sea foods	290
shrimp	180

Sum of all child and parent in hierarchical data

1 solution

Solution 1

Add your solution here

Preview 0

Sum of all child and parent in hierarchical data

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us