0

It is necessary to calculate how many products were created in each category, of course, that the products created in a child must be considered for the parent. How can i do that SQL/Postgresql?

Second explanation if first is not clear - I should create a query that will calculate products count based on a categoryId and categoryIds childs too.

Database schema

Table Category
-------------
id
name
parentId
-------------
ROWS
1 rootCategory null
2 child1 1
3 child2 2




Table Product
------------
id
name
categoryId
------------
ROWS
1 productOne 1
2 productTwo 2
3 productOne 2
4 productOne 3

If categoryId = 1 is selected it should display products count from this category ant its children. In this example answer for root category count should be 4, because it has also two subcategories that have also three items in product table.

1
0

Use a recursive CTE:

WITH RECURSIVE cats AS (
   SELECT 1::bigint AS id
 UNION
   SELECT c.id
   FROM category AS c
      JOIN cats ON c.parentid = cats.id
)
SELECT count(*)
FROM product
   JOIN cats ON cats.id = product.categoryid;

This query uses category id number 1 as an example. Replace it with the appropriate category id or use a parameter instead.

2
  • My query counts everything under category 1 if you need a different one, replace "1" with the id you want. – Laurenz Albe Feb 20 '20 at 9:29
  • You saved my ass buddy! THANK YOU! – boris Feb 20 '20 at 9:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.