Hello.

I have the following task now.

| id | parent_id | cat_title | ... |

This is a table of rubrics; there are rubrics of the 1st level and the 2nd, there is no deeper nesting. By parent_id it is determined which rubric is the parent for this one.

 | id | cat_id | item_title | ... |

this is a table of goods, belonging to the category is determined by cat_id.

I select all existing (even empty) headings as a list, and consider how many goods in each of them are like this:

 SELECT cat.*, COUNT(item.id) AS count FROM cats_table cat LEFT JOIN items_table item ON cat.id = item.cat_id

The output is like this:

Cars (0)
- Subaru (4)
- Fiat (8)
Motorcycles (3)
- Kawasaki (14)

Everything seems to be in order, there is one question - how in the parent category to calculate the total number of products from the subheadings? In this example, the idea should be Cars (12) and Motorcycles (17)

UPD Or maybe somehow I need to process the result of the request? Because no matter how much I smoke manuals, I did not find it to be done in the request itself.

Thanks to everyone who responded!

    0