Solved

Categories AND Subcategories

Posted on 2004-03-31
8
645 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Truncate vs Delete 63 87
Convert column to int 13 27
Access recordset not updateable 8 38
Copy Database Wizard Error 3 20
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

707 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

12 Experts available now in Live!

Get 1:1 Help Now