Recursive calculation to form a tree using sql

Question

I am working on a simple problem and wanted to solve it using SQL. I am having 3 tables Category, Item & a relational table CategoryItem. I need to return count of items per category but the twist is Categories are arranged in Parent-Child relationships and the count of items in child categories should be added to the count in its parent Category. Please consider the sample data below and the expected resultset using SQL.

Id  Name    ParentCategoryId
1   Category1   Null
2   Category1.1 1
3   Category2.1 2
4   Category1.2 1
5   Category3.1 3

ID  CateoryId   ItemId
1   5              1
2   4              2
3   5              2
4   3              1
5   2              3
6   1              1
7   3              2

Result:

CategoryNAme     Count
Category1          7
Category1.1        5
Category2.1        4
Category1.2        1
Category3.1        2

I can do it in my business layer but performance its not optimal because of size of data. I am hoping if I can do it in data layer, I would be able to improve performance greatly.

Thanks in Advance for your reply

You've tagged the post with both sql-server and plsql which indicates Oracle. Which one is it? They use different procedural languages and are quite different. — jpw
– jpw, Commented Feb 22, 2014 at 16:50
i am using sqlserver i wanted to get this done either by using sql or stored procedure written in Sql Server — user2443648
– user2443648, Commented Feb 22, 2014 at 16:54
@user2443648 You need to clearly define tables and name them to understand better. — IndoKnight
– IndoKnight, Commented Feb 22, 2014 at 17:17
I can't reconcile your figures. Say for Category 1, how did you get a count of 7? Category 7 has an Id of 1. If I look in your relation table, there is one instance of that category Id with an item count of 1. Please clarify. Also recursion in sql is tricky (imo). — user3248346
– user3248346, Commented Feb 22, 2014 at 17:28
@IftikharKhan Category1 is root category(assume its root node of a tree) so it will have sum of counts of items in all categories. So the sum for categoryId 1 will be Count in Categoryid2 + count in categoryId3 + count in categoryId 4 + count in categoryId 5 i.e. 1+2+1+2 + count in category1 i.e. 1 = 7. Please see parentCategoryId for reference — user2443648
– user2443648, Commented Feb 22, 2014 at 17:39

Siddique Mahsud · Accepted Answer · 2014-02-23 07:18:48Z

your tables and sample data

create table #Category(Id int identity(1,1),Name Varchar(255),parentId int)
INSERT INTO #Category(Name,parentId) values
('Category1',null),('Category1.1',1),('Category2.1',2),
('Category1.2',1),('Category3.1',3)

create table #CategoryItem(Id int identity(1,1),categoryId int,itemId int)
INSERT INTO #CategoryItem(categoryId,itemId) values
(5,1),(4,2),(5,2),(3,1),(2,3),(1,1),(3,2)

create table #Item(Id int identity(1,1),Name varchar(255))
INSERT INTO #Item(Name) values('item1'),('item2'),('item3')

Checking for all childs of parent by Recursive Commom Table Expressions

;WITH CategorySearch(ID, parentId) AS
(
SELECT ID, ID AS ParentId FROM #Category
UNION ALL
SELECT CT.Id,CS.parentId  FROM #Category CT
INNER JOIN CategorySearch CS ON CT.ParentId = CS.ID
)
select * from CategorySearch order by 1,2

Output: All child records against parent

ID  parentId
1   1
2   1
3   1
4   1
5   1
2   2
3   2
5   2
3   3
5   3
4   4
5   5

Final query for your result, count all items for category and its children categories.

;WITH CategorySearch(ID, parentId) AS
(
SELECT ID, ID AS ParentId FROM #Category
UNION ALL
SELECT CT.Id,CS.parentId  FROM #Category CT
INNER JOIN CategorySearch CS ON CT.ParentId = CS.ID
)
SELECT CA.Name AS CategoryName,count(itemId) CountItem
FROM #Category CA 
INNER JOIN CategorySearch CS ON CS.ParentId = CA.id
INNER JOIN #CategoryItem MI ON MI.CategoryId =CS.ID
GROUP BY CA.Name

Output:

CategoryName    CountItem
Category1           7
Category1.1         5
Category1.2         1
Category2.1         4
Category3.1         2

ivankristof · Accepted Answer · 2014-02-22 19:48:39Z

with help of CTE (common table expression) with recursion, you could achieve what you are looking for.

see Microsoft help for more details retated to recursive CTEs: CTE MS SQL 2008 +

hereby you could find complete example, with your sample data:

-- tables definition 
SELECT 1 as id, 'cat1' as [name],NULL as id_parent
into cat
union
select 2, 'cat1.1', 1
union
select 3, 'cat2.1', 2
union
select 4, 'cat1.2', 1
union
select 5, 'cat3.1', 3

select 1 as id , 5 as id_cat, 1 as id_item
iNTO item
UNION
select 2, 4, 2
UNION
select 3, 5, 2
UNION
select 4, 3, 1
UNION
select 5, 2, 3
UNION
select 6, 1, 1
UNION
select 7, 3, 2

-- CTE to get desired result
with childs
as
(
    select c.id, c.id_parent
    from cat c 
    UNION ALL
    select s.id, p.id_parent
    from cat s JOIN childs p
        ON (s.id_parent=p.id)
),
category_count
AS
(
    SELECT c.id, c.name, count(i.id) as items
    from cat c left outer join item i
    on (c.id=i.id_cat)
    GROUP BY c.id,c.name
),
pairs
AS
(
    SELECT id, ISNULL(id_parent,id) as id_parent
    FROM childs 
)

select p.id_parent, n.name, sum(items)
from pairs p JOIN category_count cc
    ON (p.id=cc.id)
    join cat n ON (p.id_parent=n.id)
GROUP by p.id_parent ,n.name
ORDER by 1;

Collectives™ on Stack Overflow

Recursive calculation to form a tree using sql

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related