Solved

Categories AND Subcategories

Posted on 2004-03-31
8
650 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
 

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

867 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now