?
Solved

sql count all products in sub sections

Posted on 2012-09-17
11
Medium Priority
?
787 Views
Last Modified: 2012-09-19
Hi I have 2 tables.
1 called product Sections
2 called products.

I am creating the menu on the website and want to show all products in that section and all sub sections.

In product sections there is a SectionID and ParentSectionID, in the products there is a ParentSectionID

How do i write a query to loop through all the sub sections and count the products.
0
Comment
Question by:taz8020
  • 6
  • 5
11 Comments
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 38407089
You posted to the general MS SQL Server topic, but since at least 2005 we have CTE for such recursive queries.

table schema assumed:
products:             id, name, parentsectionid
productsections: id, name, parentsectionid

WITH    allproducts AS 
        ( 
        SELECT
		   products.id as productid, 
		   products.name, 
		   productsections.id as sectionid, 
		   productsections.parentsectionId, 
		   productsections.name as section
        FROM    products 
		join    productsections 
		on      productsections.id = products.parentsectionid
        UNION ALL 
        SELECT  
		   allproducts.productid, 
		   allproducts.name, 
		   productsections.id as sectionid, 
		   productsections.parentsectionId, 
		   productsections.name as section
        FROM    allproducts 
        JOIN    productsections
        ON      productsections.id = allproducts.parentsectionId and not allproducts.parentsectionId is null 
        ) 
SELECT  sectionid, section, count(*) as productcount
FROM    allproducts 
Group By sectionid, section
order by sectionid

Open in new window


Here productcount will include the count of all subsection, subsubsection etc. products.
So if you display a treeview for navigation of product sections you can display the total count of produts in all child nodes at a root node.

If you just need the direct count it's of course simply select parentsectionid, count(*) as productcount from products group by parentsectionid.

Bye, Olaf.
0
 
LVL 3

Author Comment

by:taz8020
ID: 38407249
Hi Olaf, Thank you for that. I had to chage it a little as in products I call it websection not parentsectionId which I thought it was. I must have done something wrong as getting error:
Types don't match between the anchor and the recursive part in column "ShortDescription" of recursive query "allproducts".


WITH    allproducts AS
        (
        SELECT
               Products.ProductID,
               Products.ShortDescription,
               ProductSections.SectionID,
               ProductSections.ParentSectionID,
               ProductSections.SectionName
        FROM    Products
            join    ProductSections
            on      ProductSections.SectionID = Products.WebSection
        UNION ALL
        SELECT  
               allproducts.ProductID,
               allproducts.SectionName,
               ProductSections.SectionID,
               ProductSections.ParentSectionID,
               ProductSections.SectionName
        FROM    allproducts
        JOIN    productsections
        ON      productsections.SectionID = allproducts.ParentSectionID and not allproducts.ParentSectionID is null
        )
SELECT  SectionID, SectionName, count(*) as productcount
FROM    allproducts
Group By SectionID, SectionName
order by SectionID

Plus would you recommend looking this up each time or putting count values every so often in a coloumn call productcount in the sections table. There are a lot of sections and products so dont want to slow it down too much.
0
 
LVL 3

Author Comment

by:taz8020
ID: 38407289
ok now i get Could not find stored procedure 'ParentSectionID'
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 3

Author Comment

by:taz8020
ID: 38407318
Sorry being dull I got it know thank you so much. Its a lot quicker than I thought. would you run this each time or save the values in the productSectionTable?
0
 
LVL 3

Author Comment

by:taz8020
ID: 38407338
and if i was going to turn this into an update table how do i do it
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 38407574
You could store the counts into fields, true. If products change seldom that would be a good thing from the performance perspective.

I don't understand what you mean with update. This is a query counting how many products belong to a section, as long as you don't define fields to store that counts there is nothing to update, you count the live data to get the newest exact count values.

If you define such fields to update them you update them from the result set of this query. You can use this whole query as a subquery of a normal UPDATE-SQL.
It may be more convenient to put this CTE query into a view or a table valued function and join it in the Update-SQL.

Bye, Olaf.
0
 
LVL 3

Author Comment

by:taz8020
ID: 38411250
sorry i meant to speed up the performance, i have added a coloumn called ProductCount in the ProductsSections table. How would I change the query to update these values all in one query. so use waht you posted but change it to update the productcount.
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 38411759
update ProductsSections set ProductCount = query.productcount
from (...cte...) as query
where query.SectionID = ProductsSections.ID

Where ...cte... is to be replaced with the full code above or with the call to a table valued function.

It's as simply as a normal update. You can always take any query, put it in parenthesis and add an alias name at the closing bracket to adress it as if it was a table. So you can join it, query from it etc.

Bye, Olaf.
0
 
LVL 30

Accepted Solution

by:
Olaf Doschke earned 2000 total points
ID: 38411785
Correction, with a CTE you still need the definition of allproducts first, then the update:

WITH    allproducts AS 
        ( 
...as is...
        ) 
Update ProductSections set ProductCount = query.ProductCount
From ProductSections Left Join
(SELECT  SectionID, count(*) as ProductCount
FROM    allproducts 
Group By SectionID) as query
on query.SectionID = ProductSections.ID

Open in new window

You can shorten the CTE for allproducts to not contain the section name, as that is not needed to compute the count, of course. That should already make it a tidbit faster. All you really need in allproducts is the sectionid and the parentsectionid to make the recursive join in the union.

Bye, Olaf.
0
 
LVL 3

Author Closing Comment

by:taz8020
ID: 38412837
Thank you so much I have a nothe question that I think you will be able to answer, have posted it here.
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_27870105.html
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 38413266
Sorry, EE reports "Question Deleted" on that link.
I'll look into "Activity" in your EE user profile.

Bye, Olaf.

PS: If that was the question "SQL get all the products in a sect…"
that would be as simple as
Select * from products where SectionID = some id

Open in new window

For the direct section.
Or again
With allproducts
(
...
)
Select * from allproducts Where SectionID = some id

Open in new window


Which by the recursive nature of the query should also contain products of subsections.

To get to know your CTE better, why not simply do
With allproducts
(
...
)
Select * from allproducts

Open in new window

That makes you see what's in this table expression and you can do any queries on that, like the count query, the update or any other query, as if it was a real table.

Bye, Olaf.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

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 ?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

807 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