SQL Server Count records from parent category and all subcategories
I currently have a stored procedure where I create a table and query the table to get the desired result, the result of which is an infinitely layered child / parent table that allows me to display data in my ASP Classic based web page.
This procedure:
SET NOCOUNT ON;
DECLARE @Categories TABLE(
CatID INT NOT NULL,
CatName VARCHAR(200) NOT NULL,
ParentID INT
)
INSERT INTO @Categories
SELECT CatID, CatName, ParentID = NULL FROM Categories
WHERE CatName NOT IN (
SELECT CatName FROM Categories c
INNER JOIN CategoriesRel r ON c.CatID = r.ChildID)
UNION
SELECT CatID, CatName, cr.ParentID FROM Categories
INNER JOIN CategoriesRel cr ON cr.ChildID = Categories.CatID
ORDER BY CatID;
WITH r AS (
SELECT CatID, CatName, ParentID, depth=0 ,Sort=CAST(CatName As VARCHAR(MAX))
FROM @Categories WHERE ParentID IS NULL
UNION ALL
SELECT c.CatID, c.CatName, c.ParentID, Depth=r.Depth+1 ,Sort=r.Sort+CAST(c.CatName AS VARCHAR(200))
FROM r INNER JOIN @Categories c ON r.CatID=c.ParentID WHERE r.Depth<32767
)
SELECT CatID, CatName=replicate('-',r.Depth*3)+r.CatName,
(SELECT COUNT(BsnID) FROM Businesses WHERE Businesses.CatID = r.CatID) AS CatCount
FROM r ORDER BY Sort OPTION(maxrecursion 32767);
The problem with this is the count request at the bottom.
(SELECT COUNT(BsnID) FROM Businesses WHERE Businesses.CatID = r.CatID) AS CatCount
If I use this particular piece of code, I only get the number of rows returned for a specific category ID. For example, this is the current result:
CatID | CatName | CatCount
______|______________________|_________
1016 | Antiques | 1
1021 | Automotive | 1
1024 | ---Repair | 1
1026 | ------Engine Repair | 1
1035 | ---Tyres | 1
1002 | Building | 0
I need the result to be something like this:
CatID | CatName | CatCount
______|______________________|_________
1016 | Antiques | 1
1021 | Automotive | 4
1024 | ---Repair | 2
1026 | ------Engine Repair | 1
1035 | ---Tyres | 1
1002 | Building | 0
Any help would be greatly appreciated! Thanks to
EDIT: Included with SQL for testing
CREATE TABLE Categories(CatID int NOT NULL,CatName nvarchar(100) NOT NULL,PRIMARY KEY (CatID));
CREATE TABLE CategoriesRel(CatLinkID int NOT NULL,ParentID int NOT NULL,ChildID int NOT NULL,PRIMARY KEY (CatLinkID),FOREIGN KEY (ParentID) REFERENCES Categories(CatID),FOREIGN KEY (ChildID) REFERENCES Categories(CatID);
CREATE TABLE Businesses(BsnID int NOT NULL,BsnName nvarchar(100) NOT NULL,CatID int NOT NULL,PRIMARY KEY (BsnID),FOREIGN KEY (CatID) REFERENCES Categories(CatID);
INSERT INTO Categories VALUES ('1','Antique'),('2','Automotive'),('3','Building'),('4','Tyres'),('5','Repair'),('6','Engine Repairs');
INSERT INTO CategoriesRel VALUES ('1', '2','4'),('1','2','5'),('1','5','6');
INSERT INTO Businesses VALUES ('1','Test1','2'),('2','Test2','4'),('3','Test3','5'),('4','Test4','6');
source to share
Take out the WHERE ParentID IS NULL
CTE part and add the field RootId
. This will allow you to find the number of children for each parent level.
;WITH r AS (
SELECT CatID, CatName, ParentID, CatID RootId, depth=0 ,Sort=CAST(CatName As VARCHAR(MAX))
FROM @Categories
--WHERE ParentID IS NULL
UNION ALL
SELECT c.CatID, c.CatName, c.ParentID, RootId, Depth=r.Depth+1 ,Sort=r.Sort+CAST(c.CatName AS VARCHAR(200))
FROM r
INNER JOIN @Categories c ON r.CatID=c.ParentID
WHERE r.Depth<32767
)
This gives you a row for each level of the hierarchy. Parent categories appear multiple times, but with different RootIds. This will allow us to get an account at each level of the hierarchy:
+-------+---------------+----------+--------+-------+-------------------------------+
| CatID | CatName | ParentID | RootId | depth | Sort |
+-------+---------------+----------+--------+-------+-------------------------------+
| 1002 | Building | NULL | 1002 | 0 | Building |
| 1016 | Antiques | NULL | 1016 | 0 | Antiques |
| 1021 | Automotive | NULL | 1021 | 0 | Automotive |
| 1024 | Repair | 1021 | 1024 | 0 | Repair |
| 1026 | Engine Repair | 1024 | 1026 | 0 | Engine Repair |
| 1035 | Tyres | 1021 | 1035 | 0 | Tyres |
| 1026 | Engine Repair | 1024 | 1024 | 1 | RepairEngine Repair |
| 1024 | Repair | 1021 | 1021 | 1 | AutomotiveRepair |
| 1035 | Tyres | 1021 | 1021 | 1 | AutomotiveTyres |
| 1026 | Engine Repair | 1024 | 1021 | 2 | AutomotiveRepairEngine Repair |
+-------+---------------+----------+--------+-------+-------------------------------+
If you group the RootId and get it COUNT()
, it gives you the numbers you are looking for:
select RootId, count(b.CatId) catCount
from r
left outer join Businesses b on r.CatID = b.CatId
group by rootid
+--------+----------+
| RootId | CatCount |
+--------+----------+
| 1002 | 0 |
| 1016 | 1 |
| 1021 | 4 |
| 1024 | 2 |
| 1026 | 1 |
| 1035 | 1 |
+--------+----------+
The rest of the query is just sorting and changing the CatName. You want to get the deepest baby for each category:
select r.CatId, CatName, max(depth) MaxDepth
from r
group by r.catId, CatName
End request:
SELECT y.CatID,
replicate('-',y.MaxDepth*3)+y.CatName CatName,
x.CatCount
FROM (select RootId, count(b.CatId) catCount
from r
left outer join Businesses b on r.CatID = b.CatId
group by rootid) x
join (select r.CatId, CatName, max(depth) MaxDepth
from r
group by r.catId, CatName) y on y.CatID = x.RootId
order by (select Sort from r where r.CatID = x.RootId and r.depth = y.MaxDepth)
OPTION(maxrecursion 32767);
source to share