Solved

Categories AND Subcategories

Posted on 2004-03-31
8
665 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

738 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