DECLARE @Category TABLE(CategoryID INT, ParentID INT, CategoryText VARCHAR(55), Lineage VARCHAR(55), SortOrder INT)

INSERT @Category(CategoryID, ParentID, CategoryText, Lineage, SortOrder)
	SELECT 1, 0, 'All', '1', 0 UNION ALL
	SELECT 2, 1, 'Beer', '1.1', 1 UNION ALL
	SELECT 3, 2, 'Domestic', '1.1.1', 2 UNION ALL
	SELECT 37, 1, 'Wine', '1.4', 38 UNION ALL
	SELECT 38, 37, 'Domestic', '1.4.1', 39 UNION ALL
	SELECT 39, 38, 'California', '1.4.1.1', 40

DECLARE @Sales TABLE(SalesID INT, ProductID INT, Quantity INT, Price MONEY, Cost MONEY)

INSERT @Sales(SalesID, ProductID, Quantity, Price, Cost)
	SELECT 20935, 1554, 1, 6.99, 4.91 UNION ALL
	SELECT 20935, 721, 1, 10.99, 8 UNION ALL
	SELECT 20934, 786, 1, 9.99, 7.35 UNION ALL
	SELECT 20934, 786, 2, 9.99, 7.35 UNION ALL
	SELECT 20933, 1554, 1, 6.99, 4.91 UNION ALL
	SELECT 20933, 1554, 1, 5.99, 4.91 --UNION ALL
--	SELECT 20933, 1554, 1, 5.99, 4.91

DECLARE @Products TABLE(ProductID INT, Product VARCHAR(55), CategoryID INT)

INSERT @Products(ProductID, Product, CategoryID)
	SELECT 1554, 'Woodbridge Chardonnay, 750 ml', 39 UNION ALL
	SELECT 721, 'SHARPE HILL RED SERAPH, 750 ml', 38 UNION ALL
	SELECT 786, 'BUD , 12 oz 12 pack Bottle(s)', 3

SELECT
	SalesID,
	CategoryID,
	ParentID,
	CategoryText,
	Lineage,
	SortOrder, 
	SUM(Quantity) AS Quantity,
	SUM(Price) AS Price,
	SUM(Cost) AS Cost,
	PriceMonitor
FROM (
	SELECT
		A.PriceMonitor,
		A.SalesID,
		CASE 
			WHEN (A.GRP = 1) THEN C.CategoryID
			ELSE -1
		END AS CategoryID,
		CASE 
			WHEN (A.GRP = 1) THEN C.ParentID
			ELSE Max(ISNULL(A.ParentID,0))
		END AS ParentID,
		CASE 
			WHEN (A.GRP = 1) THEN C.CategoryText
			ELSE A.Product
		END AS CategoryText,
		CASE 
			WHEN (A.GRP = 1) THEN MAX(ISNULL(C.Lineage,0))
			ELSE MAX(A.Lineage)
		END AS Lineage,
		CASE 
			WHEN (A.GRP = 1) THEN ISNULL(C.SortOrder,0) * 100
			ELSE MAX(A.SortOrder)
		END AS SortOrder,
		SUM(Quantity) AS Quantity,
		SUM(Price) AS Price,
		SUM(Cost) AS Cost
	FROM 
		@Category C 
		RIGHT JOIN (
			SELECT
				CASE
					WHEN P.CategoryID <> -1 THEN Price
					ELSE P.CategoryID
				END AS PriceMonitor,
				SP.SalesID,
				MAX(P.CategoryID) AS ParentID,
				SUM(SP.Quantity) AS Quantity,
				SUM(SP.Price*SP.Quantity) AS Price,
				SUM(SP.Cost*SP.Quantity) AS Cost,
				CASE 
					WHEN (GROUPING(P.Product) = 1) THEN 
					CASE 
						WHEN(C.Lineage IS NULL) THEN 0
						ELSE Max(C.SortOrder) * 100
						END
					ELSE (Max(C.SortOrder) * 100) + 1
				END AS SortOrder,
				GROUPING(P.Product) as grp,
				GROUPING(SP.Price) as grp2,
				P.Product AS Product,
				CASE 
					WHEN (GROUPING(P.Product) = 1) THEN ISNULL(C.Lineage,0)
					ELSE 'X.' + ISNULL(C.Lineage,0)
				END AS Lineage,
				CASE 
					WHEN(GROUPING(P.Product) = 1) THEN 
					CASE 
						WHEN(C.Lineage IS NULL) THEN 'UNKNOWN'
						ELSE MAX(C.CategoryText)
					END
					ELSE ISNULL(P.Product, 'UNKNOWN')
				END AS Category
			FROM 
				@Sales SP 
				INNER JOIN @Products P ON SP.ProductID = P.ProductID 
				INNER JOIN @Category C ON P.CategoryID = C.CategoryID
			GROUP BY 
				SP.SalesID, 
				C.Lineage, 
				P.Product, 
				SP.Price,
				P.CategoryID
			WITH ROLLUP
			HAVING 
				((GROUPING(SP.Price) = 0) 
				OR (GROUPING(SP.Price) = GROUPING(P.Product)))
   			) A ON SUBSTRING(A.Lineage, 0, LEN(C.Lineage) + 1) = C.Lineage
	GROUP BY 
		A.SalesID, 
		C.ParentID, 
		C.CategoryID, 
		C.CategoryText,
		C.SortOrder, 
		A.Product, 
		A.GRP, 
		A.Price,
		A.PriceMonitor) Z
WHERE 
	CategoryID IS NOT NULL
	AND (CategoryID <> -1 OR PriceMonitor IS NOT NULL)
GROUP BY
	SalesID,
	CategoryID,
	ParentID,
	CategoryText,
	Lineage,
	SortOrder,
	PriceMonitor
ORDER BY 
	SalesID DESC, 
	SortOrder, 
	Price

quote:

---

4. I don't believe you were aggregating the prices correctly in the original derived table. I placed logic to multiply cost and price by the quantity.

---