Cette requete prestashop permet de savoir combien de references ont été acheté par un client. J'ai fait un pivot sur les références avec la somme de produit acheté.

nbAchatRefParClient_prestashop_Pivot_mysql.sql

SET group_concat_max_len = 18446744073709551615;
SET @COLUMNS = NULL;


SELECT GROUP_CONCAT(DISTINCT
        CONCAT(
        'SUM(IF(D.id_product = "',D.id_product,'", B.product_quantity, 0)) AS "',D.reference,'"'
		)
) INTO @COLUMNS
FROM ps_product D
WHERE D.reference IN ('REF1','REF2');

SET @SQL = CONCAT("
SELECT
	   C.company,
	   C.firstname,
	   C.lastname,
	   C.email,	 
	   C.id_customer,  
	   ",@COLUMNS,"
	FROM ps_orders A 
	INNER JOIN ps_order_detail B ON B.id_order = A.id_order
	INNER JOIN ps_customer C ON C.id_customer = A.id_customer
	INNER JOIN ps_product D ON D.id_product = B.product_id
WHERE D.reference IN ('REF1','REF2')
GROUP BY C.id_customer
");

SELECT @SQL;

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;