sql count all products in sub sections

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

Select allOpen 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.

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.

ok now i get Could not find stored procedure 'ParentSectionID'

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?

and if i was going to turn this into an update table how do i do it

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.

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.

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.

Thank you so much I have a nothe question that I think you will be able to answer, have posted it here.
https://www.experts-exchange.com/questions/27870105/SQL-get-all-the-products-in-a-section-and-sub-sections.html

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

Select allOpen in new window

For the direct section.
Or again

With allproducts
(
...
)
Select * from allproducts Where SectionID = some id

Select allOpen 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

Select allOpen 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.