Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Categories AND Subcategories

Posted on 2004-03-31
8
Medium Priority
?
683 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
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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 ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

885 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