How to make COUNTs for every 2 JOINs?

Question

MySQL

Question only for GURU.
Dear gurus how to use multiple COUNTs in a query with multiple JOINs?
The fact is that this request is built into the CMS and I can only change this type of request. This is real?
Can this be done without nested queries like ??? taskFROM (SELECT)

SELECT COUNT(order.id), COUNT(cat.id), prod.name, prod.product_id
FROM product prod
LEFT JOIN produuct_to_category prod_cat USING (product_id) 
LEFT JOIN caterory cat ON prod_cat.category_id = cat.category_id
LEFT JOIN order USING  (product_id) 
GROUP BY prod.product_id;

This query returns the result:

count(order.id),        count(cat.id),        name,        product_id
198                       198               Продукт1                 1
33                        33                   Прод2                 2
42                        42                   Прод3                 3

And should be something like this:

count(order.id),        count(cat.id),        name,        product_id
6                       33               Продукт1                 1
11                      3                   Прод2                 2
7                       6                   Прод3                 3

Answer 1

Try something like this:

sum (case when order.id is null then 0 else 1 end) order,
sum (case when cat.id is null then 0 else 1 end) cat,
prod.name, 
prod.product_id