Solved

Categories AND Subcategories

Posted on 2004-03-31
8
670 Views
Last Modified: 2008-02-26
Dear all,

If I have:
Table Categories:( CategoryID{PK}, ParentCategoryID{FK to CategoryID in same table})  
Table Products   :( ProductID{PK},   CategoryID{FK to Categories table})
How can get product count under given categoryID + subcategoies below it

Thanks.
0
Comment
Question by:ethar1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 12

Expert Comment

by:monosodiumg
ID: 10723299
Assuming categories that are not subcategories have a Null parentcategoryID:

Select isnull(C.ParentCategoryID, C.CategoryID), count(*)
from Categories C left join Products P on P.CategoryID = C.CategoryID
group by isnull(C.ParentCategoryID, C.CategoryID)

If instead the parentcategoryID is the same as the categoryID for the non-sub categories:
Select C.ParentCategoryID, count(*)
from Categories C left join Products P on P.CategoryID = C.CategoryID
group by isnull(C.ParentCategoryID, C.CategoryID)
0
 

Author Comment

by:ethar1
ID: 10724498
Dear monosodiumg,
Thanks for your reply.
 If i have the following data
Categories:
CategoryID -ParentCategoryID
25      NULL
26      25
27      26
28      27
29      28
30      29
31      30
Products:
ProductID       -CategoryID
P1      25
P2      25
P3      26
P4      27
P5      28
P6      29
P7      30

now , the result I want to be like this :
CategoryID -ProductsCount
25      2 + 1 + 1 + 1 + 1 + 1 ( 7 )
26      1 + 1 + 1 + 1+ 1 ( 5)
27      1 + 1 + 1 + 1  ( 4)
28      1 + 1 + 1  ( 3)
29      1 + 1   ( 2)
30      1  ( 1)
31      0
Thanks.
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 10739272
Ahhh! the old tree problem. You'll find a dozen solutions here on EE and a hundreds more elsewhere. This crops up at least once a month ;)

You could use  recursive procedure but that would be limited to 30 levels (I think that's the recursion depth limit in SQL 2K) and would involve executing a lot of queries.
Another appraoch, which I am going to take, is to build a stack of the nodes by level, then run a totalling query over each level,  from the deepst on to the root.
In the solution below I'm copying all the info required into my stack table to avoid later joining with other tables. This isn't the most efficnetn space wise but it will perform OK.


create table #CatStack (CategoryID integer, ParentCategoryID integer, Depth integer, ProdCount Integer)

declare @depth integer
set @depth =0
--initialise with top level categories:
insert  #CatStack
select C.CategoryID, null, @depth, count(P.*)
from Categories C left join Products P on C.CategoryID = P.CategoryID
where ParentCategoryID is null
group by C.CategoryID, null, @depth

--add the other levels:
while @@rowsaffected > 0
begin
      set @depth = @depth + 1
      insert  #CatStack
      select C.CategoryID, S.CategoryID, @depth,  count(P.*)
      from #CatStack S inner join Categories C on S.categoryID = C.parentCategoryID left join Products P on C.CategoryID = P.CategoryID
      where S.depth = @depth - 1
      group by C.CategoryID, S.CategoryID, @depth
end

--@depth is now the depth of the tree.

--sum from the lowest level up:
while @depth  >= 0
begin
      update #CatStack
      set ProdCount  = S.ProdCount  +
            (select sum(S2.ProdCount)  
                  FROM #CatStack S2 on S.categoryID = S2.parentCategoryID)
      FROM #CatStack S , #CatStack S2
      where S.depth = @depth

      set @depth = @depth - 1
end

--#CatStack should now contain the total number of products for each category.
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

Author Comment

by:ethar1
ID: 10740039
Thanks,
But wot's @@rowsaffected ?

This is list of errors :
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '*'.
Server: Msg 137, Level 15, State 1, Line 13
Must declare the variable '@@rowsaffected'.
Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near '*'.
Server: Msg 156, Level 15, State 1, Line 31
Incorrect syntax near the keyword 'on'.
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 10740481
Sorry.
@@rowsaffected should be @@rowcount! Getting mixed up with Oracle.
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 10740508
create table #CatStack (CategoryID integer, ParentCategoryID integer, Depth integer, ProdCount Integer)

declare @depth integer
set @depth =0
--initialise with top level categories:
insert  #CatStack
select C.CategoryID, null, @depth, count(P.CategoryID)
from Categories C left join Products P on C.CategoryID = P.CategoryID
where ParentCategoryID is null
group by C.CategoryID, null, @depth

--add the other levels:
while @@rowcount > 0
begin
     set @depth = @depth + 1
     insert  #CatStack
     select C.CategoryID, S.CategoryID, @depth,  count(P.CategoryID)
     from #CatStack S inner join Categories C on S.categoryID = C.parentCategoryID left join Products P on C.CategoryID = P.CategoryID
     where S.depth = @depth - 1
     group by C.CategoryID, S.CategoryID, @depth
end

--@depth is now the depth of the tree.

--sum from the lowest level up:
while @depth  >= 0
begin
     update #CatStack
     set ProdCount  = S.ProdCount  +
          (select sum(S2.ProdCount)  
               FROM #CatStack S2
              where S.categoryID = S2.parentCategoryID)
     FROM #CatStack S , #CatStack S2
     where S.depth = @depth

     set @depth = @depth - 1
end

0
 

Author Comment

by:ethar1
ID: 10740544
Server: Msg 164, Level 15, State 1, Line 10
GROUP BY expressions must refer to column names that appear in the select list.
Server: Msg 164, Level 15, State 1, Line 21
GROUP BY expressions must refer to column names that appear in the select list.
0
 
LVL 12

Accepted Solution

by:
monosodiumg earned 500 total points
ID: 10740915
create table #CatStack (CategoryID integer, ParentCategoryID integer, Depth integer, ProdCount Integer)

declare @depth integer
set @depth =0
--initialise with top level categories:
insert  #CatStack
select C.CategoryID, null, @depth, count(P.CategoryID)
from Categories C left join Products P on C.CategoryID = P.CategoryID
where ParentCategoryID is null
group by C.CategoryID

--add the other levels:
while @@rowcount > 0
begin
     set @depth = @depth + 1
     insert  #CatStack
     select C.CategoryID, S.CategoryID, @depth,  count(P.CategoryID)
     from #CatStack S inner join Categories C on S.categoryID = C.parentCategoryID left join Products P on C.CategoryID = P.CategoryID
     where S.depth = @depth - 1
     group by C.CategoryID, S.CategoryID
end

--@depth is now the depth of the tree.

--sum from the lowest level up:
while @depth  >= 0
begin
     update #CatStack
     set ProdCount  = S.ProdCount  +
          (select sum(S2.ProdCount)  
               FROM #CatStack S2
              where S.categoryID = S2.parentCategoryID)
     FROM #CatStack S , #CatStack S2
     where S.depth = @depth

     set @depth = @depth - 1
end
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question