Click here to Skip to main content
14,036,604 members
Rate this:
 
Please Sign up or sign in to 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
Updated 30-Sep-18 8:31am
v3

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Check this:
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
   
Comments
maysamfth 1-Oct-18 3:56am
   
perfect. so thanks. It's worked.
Maciej Los 1-Oct-18 10:34am
   
You're very welcome.

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web04 | 2.8.190424.1 | Last Updated 30 Sep 2018
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100