I'm trying to provide reporting functionality on a typical restaurant type database. I describe the specifics of the problem below, but in a nutshell I need to be able retrieve aggregate data (sums and counts) for items that relate to a heirarchical self-joining "Category" table. I know that's wordy and probably confusing, so I'll try to relay the details with an example. I have four tables specific to this problem:
Categories Id Name ParentId 1 Food NULL 2 Drinks NULL 3 Beer 2 4 Draft Beer 3 5 Bottle Beer 4 6 Pizza 1 8 Sandwiches 1
ParentId is a FK back to Categories
MenuItems Id Name CategoryId 1 6" Sausage 6 2 French Dip 8 3 Dogfish 60 IPA 4 4 Dogfish 60 IPA 5 5 Bud Light 5 Orders Id Total DateReceived DateCompleted 1 12.00 1/1/1970 1:00 1/1/1970 1:05 2 11.50 1/1/1970 1:08 1/1/1970 1:18 OrderItems Id Price OrderId MenuItemId 1 9.00 1 1 2 3.00 1 5 3 3.50 2 3 4 8.00 2 2
The goal of this reporting functionality is to take a categoryId and return a sum (or count) over a given period of time. If the incoming category is a leaf category (e.g. Bottle Beer), I have no problem calculating this total. If however it's higher up the heirarchy, "Food" for instance, I can't figure out how to write the query in such a way that it will sum up all child categories.
I'm using SQL Server 2008r2. I tried using a WITH common_table_expression query, but I got an error about aggregate functions not allowed in the "recursive part of a recursive cte".
The SQL I have today looks something like this:
SELECT SUM(oi.Price) as Total, DAY(o.Completed) FROM Orders o JOIN OrderItems oi on oi.orderId = o.id JOIN MenuItems mi on oi.MenuItemId = mi.id JOIN Categories c on mi.CategoryId = c.id WHERE o.Completed is not null AND c.Id = @categoryId AND o.Completed between @startDate and @endDate GROUP BY DAY(o.completed)
Again, if @categoryId is a leaf category, this query gives me exactly what I'm looking for. If it's not, it returns nothing. I'd like to be able to provide @category=2 (Drinks) and get back the sum of all items anywhere in the "Drinks" category hierarchy.
You're actually very close to the mark with trying to solve this problem with the CTE recursion method. Recursion is confusing at the best of times, but is the best solution for this type of problem.
I have copied the schema you have specified in your question and have come up with the following solution.
I have tested it and it produces the following output... I am assuming that this is what you want to see...
CategoryName CategoryId TotalSaleAmount Food 1 17.00 Drinks 2 6.50 Beer 3 6.50 Draft Beer 4 3.50 Bottle Beer 5 3.00 Pizza 6 9.00 Sandwiches 8 8.00
Obviously the $17 for food is made up of pizza and sandwiches. Likewise the $6.50 for the drinks is made of the $6.50 for the beer, which in turn is made up of the $3.50 and $3.00 from the draft and bottled beer respectively.... etc etc...
This matches the hierarchy of the categories.
The code is below, this should work with your schema. If you wish to restrict the output add a where clause immediate before the group by.
WITH CategorySearch(ParentId, Id) AS ( SELECT ID AS ParentId, ID FROM Categories WHERE ID NOT IN (SELECT ParentId FROM Categories WHERE ParentId IS NOT NULL) UNION ALL SELECT Categories.ParentId, CS.Id FROM Categories Categories INNER JOIN CategorySearch CS ON Categories.Id = CS.ParentId WHERE Categories.ParentId IS NOT NULL ) SELECT CA.Name AS CategoryName, CS.ParentId AS CategoryID, SUM(OI.Price) AS TotalSaleAmount FROM Categories CA INNER JOIN CategorySearch CS ON CS.ParentId = CA.ID INNER JOIN MenuItems MI ON MI.CategoryId = CS.Id INNER JOIN OrderItems OI ON OI.MenuItemId = MI.ID INNER JOIN Orders O ON OI.OrderId = O.Id GROUP BY CA.Name, CS.ParentId ORDER BY CS.ParentId