mysql group by join_在GROUP BY中使用MySQL使用JOIN获取SUM

I have two tables in MySQL 5.1.38.

products

+----+------------+-------+------------+

| id | name | price | department |

+----+------------+-------+------------+

| 1 | Fire Truck | 15.00 | Toys |

| 2 | Bike | 75.00 | Toys |

| 3 | T-Shirt | 18.00 | Clothes |

| 4 | Skirt | 18.00 | Clothes |

| 5 | Pants | 22.00 | Clothes |

+----+------------+-------+------------+

ratings

+------------+--------+

| product_id | rating |

+------------+--------+

| 1 | 5 |

| 2 | 5 |

| 2 | 3 |

| 2 | 5 |

| 3 | 5 |

| 4 | 5 |

| 5 | 4 |

+------------+--------+

My goal is to get the total price of all products which have a 5 star rating in each department. Something like this.

+------------+-------------+

| department | total_price |

+------------+-------------+

| Clothes | 36.00 | /* T-Shirt and Skirt */

| Toys | 90.00 | /* Fire Truck and Bike */

+------------+-------------+

I would like to do this without a subquery if I can. At first I tried a join with a sum().

select department, sum(price) from products

join ratings on product_id=products.id

where rating=5 group by department;

+------------+------------+

| department | sum(price) |

+------------+------------+

| Clothes | 36.00 |

| Toys | 165.00 |

+------------+------------+

As you can see the price for the Toys department is incorrect because there are two 5 star ratings for the Bike and therefore counting that price twice due to the join.

I then tried adding distinct to the sum.

select department, sum(distinct price) from products

join ratings on product_id=products.id where rating=5

group by department;

+------------+---------------------+

| department | sum(distinct price) |

+------------+---------------------+

| Clothes | 18.00 |

| Toys | 90.00 |

+------------+---------------------+

But then the clothes department is off because two products share the same price.

Currently my work-around involves taking something unique about the product (the id) and using that to make the price unique.

select department, sum(distinct price + id * 100000) - sum(id * 100000) as total_price

from products join ratings on product_id=products.id

where rating=5 group by department;

+------------+-------------+

| department | total_price |

+------------+-------------+

| Clothes | 36.00 |

| Toys | 90.00 |

+------------+-------------+

But this feels like such a silly hack. Is there a better way to do this without a subquery? Thanks!

解决方案

Use:

SELECT p.department,

SUM(p.price) AS total_price

FROM PRODUCTS p

JOIN (SELECT DISTINCT

r.product_id,

r.rating

FROM RATINGS r) x ON x.product_id = p.id

AND x.rating = 5

GROUP BY p.department

Technically, this does not use a subquery - it uses a derived table/inline view.

Marking this as community wiki cuz some monkey keeps downvoting me though it's 100% correct.