Deux requêtes SQL à associer mais je ne sais le faire !!

[Bayside]

Bonjour, je souhaite faire une seul requête sql mais je n'ai pas toutes les informations sur une seule requête

Je voudrais avoir cette requête 

SELECT
pc.`id_customer`,
pc.`email`,
pc.`id_gender`,
pa.`firstname`,
pa.`lastname`,
pa.`postcode`,
pa.`city`,
pa.`phone`,
pa.`phone_mobile`,
pc.`birthday`

FROM ps_address pa
LEFT JOIN ps_customer pc ON pa.`id_customer`=pc.`id_customer`
WHERE
pa.`deleted`=0
AND pc.`email` != ''
AND pa.`id_supplier` = 0
AND pa.`id_manufacturer` = 0
GROUP BY pa.`address1`

 et lui ajouter la colonne total_spent de cette requête 

SELECT SQL_CALC_FOUND_ROWS 			a.`id_customer`,`lastname`,`firstname`,`email`, gl.name as title, ( 			SELECT SUM(total_paid_real / conversion_rate) 			FROM ps_orders o 			WHERE o.id_customer = a.id_customer 			 AND o.id_shop IN (1)  			AND o.valid = 1 		) as total_spent, ( 			SELECT c.date_add FROM ps_guest g 			LEFT JOIN ps_connections c ON c.id_guest = g.id_guest 			WHERE g.id_customer = a.id_customer 			ORDER BY c.date_add DESC 			LIMIT 1 		) as connect 			FROM `ps_customer` a 			 			LEFT JOIN ps_gender_lang gl ON (a.id_gender = gl.id_gender AND gl.id_lang = 1)  			 			WHERE 1 AND a.`deleted` = 0  AND `date_add` >= '2017-11-01 0:0:0' AND `date_add` <= '2019-11-07 23:59:59' 			 			 			ORDER BY `total_spent` desc

evidement je ne sais pas comment les associer

 

le but étant d'avoir un fichier client avec les informations clients mais aussi combien il a dépensé en CA

 

merci

 

merci de votre aide

Edited November 11, 2019 by Bayside (see edit history)

[Arnaud Drieux]

Bonjour.

Ajoutez cette sous-requête après votre ligne pc.`birthday`

(SELECT SUM(total_paid_real / conversion_rate)
	FROM ps_orders o 
 	WHERE o.id_customer = pc.id_customer 
	AND o.id_shop IN (1) 
	AND o.valid = 1) AS total_spent

n'oubliez pas la virgule après pc.birthday, sinon vous allez avoir une erreur.

Edited November 14, 2019 by Arnaud Drieux (see edit history)