Jump to content

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


Recommended Posts

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 by Bayside (see edit history)
Link to comment
Share on other sites

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 by Arnaud Drieux (see edit history)
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...