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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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