Obtenga SUM en GROUP BY con JOIN usando MySQL

Tengo dos tablas en 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 | +------------+--------+ 

Mi objective es get el precio total de todos los productos que tienen una calificación de 5 estrellas en cada departamento. Algo como esto.

 +------------+-------------+ | department | total_price | +------------+-------------+ | Clothes | 36.00 | /* T-Shirt and Skirt */ | Toys | 90.00 | /* Fire Truck and Bike */ +------------+-------------+ 

Me gustaría hacer esto sin una subconsulta si puedo. Al principio intenté unirme con una 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 | +------------+------------+ 

Como puede ver, el precio del departamento de juguetes es incorrecto porque hay dos clasificaciones de 5 estrellas para la bicicleta y, por lo tanto, count ese precio dos veces debido a la unión.

Luego traté de agregar distinto a la 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 | +------------+---------------------+ 

Pero luego el departamento de ropa está apagado porque dos productos comparten el mismo precio.

Actualmente, mi solución consiste en tomar algo único sobre el producto (la identificación) y usarlo para que el precio sea único.

 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 | +------------+-------------+ 

Pero esto se siente como un hack tonto. ¿Hay una mejor manera de hacer esto sin una subconsulta? ¡Gracias!

Utilizar:

  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 

Técnicamente, esto no utiliza una subconsulta: utiliza una tabla derivada / vista en línea.

Marcando esto como wiki de la comunidad porque algunos monos no dejan de votarme, aunque es 100% correcto.

La razón principal por la que tiene problemas para encontrar una solución es que el esquema presentado es fundamentalmente defectuoso. No debe permitir que una tabla tenga dos filas que sean duplicates completos entre sí. Cada tabla debe tener un medio para identificar de manera única cada fila, incluso si es la combinación de todas las columnas. Ahora, si cambiamos la tabla de ratings para que tenga una columna AUTO_INCREMENT llamada Id , el problema es más fácil:

 Select products.department, Sum(price) As total_price From products Left Join ratings As R1 On R1.product_id = products.id And R1.rating = 5 Left Join ratings As R2 On R2.product_id = R1.product_id And R2.rating = R1.rating And R2.Id > R1.Id Where R2.Id Is Null Group By products.department 

Puedes hacer dos consultas. Primera consulta:

 SELECCIONE DISTINCT product_id FROM calificaciones DONDE calificación = 5;

Luego, tome cada una de esas ID y colóquelas manualmente en la segunda consulta:

 SELECCIONAR departamento, Suma (precio) COMO total_price
 DESDE productos
 DONDE product_id In (1,2,3,4)
 GRUPO POR departamento;

Esta es la solución para no poder usar subconsultas. Sin ellos, no hay forma de eliminar los loggings duplicates causados ​​por la unión.

No puedo pensar en ninguna forma de hacerlo sin una subconsulta en algún lugar de la consulta. Quizás podría usar una Vista para enmascarar el uso de una subconsulta.

Salvo eso, la mejor opción es encontrar el set de datos mínimo necesario para hacer el cálculo y hacerlo en el frente. Si eso es posible o no depende de tus datos específicos: cuántas filas, etc.

La otra opción (de hecho, tal vez esta sea la mejor …) sería get un nuevo ORM o prescindir de él por completo;)

Esta vista le permitiría omitir la subconsulta:

 CREATE VIEW Distinct_Product_Ratings AS SELECT DISTINCT product_id, rating FROM Ratings