计算带有3个表的项目

Question

I try to calculate the number and percentage of items from 3 tables.

Tables:

categories
cat_id   | title
1        | phone
2        | tablet

products
prod_id   | cat_id | title
1001      | 1      | iphone
1002      | 1      | nokia
1003      | 1      | blackberry
1004      | 2      | ipad

user_products
id     | prod_id  | user_id
1      | 1001     | 1
2      | 1001     | 2
3      | 1001     | 3
4      | 1003     | 3
5      | 1004     | 4

What I have so far:

SELECT categories.cat_id, products.title, COUNT(products.title ) AS cnt
FROM categories
LEFT JOIN products ON categories.cat_id = products.cat_id
GROUP BY products.title

Desired result:

Tablets:
Ipad       | 1 | 100%

Phones:
Iphone     | 3 | 75%
Blackberry | 1 | 25%

Answer 1

SELECT c.cat_id, c.title AS cat_name, p.prod_id, p.title AS prod_name,
       COUNT(*) AS cnt,
       100*COUNT(*)/cat_total AS pct
FROM products AS p
INNER JOIN user_products AS up ON p.prod_id = up.prod_id
INNER JOIN (SELECT c.cat_id, c.title, COUNT(*) AS cat_total
             FROM categories AS c
             JOIN products AS p ON c.cat_id = p.cat_id
             JOIN user_products AS up ON up.prod_id = p.prod_id
             GROUP BY c.cat_id) AS c ON c.cat_id = p.cat_id
GROUP BY p.prod_id
ORDER BY cat_name, cnt DESC

DEMO