Consulta para saber cuanto dinero se han gastado todos los compradores en una determinada categoría/marca

[victorweb]

SELECT C.firstname, C.lastname, C.email , SUM(OD.total_price_tax_incl) as pvp 
FROM ps_customer C 
INNER JOIN ps_orders O on C.id_customer = O.id_customer
INNER JOIN ps_order_detail OD on O.id_order = OD.id_order
INNER JOIN ps_category_product CP ON CP.id_product = OD.product_id
WHERE CP.id_category = X
GROUP BY C.email
ORDER BY pvp desc

 

En una determinada marca:

SELECT C.email , CAST(SUM(OD.total_price_tax_incl) as decimal(8,2)) as pvp
FROM ps_customer C 
INNER JOIN ps_orders O on C.id_customer = O.id_customer
INNER JOIN ps_order_detail OD on O.id_order = OD.id_order
INNER JOIN ps_product P ON P.id_product = OD.product_id
WHERE P.id_manufacturer = X
GROUP BY C.email
ORDER BY pvp desc

 

Edited February 23, 2021 by victorweb (see edit history)