Edit History

Hi,

I am trying to export SQL via PrestaShop's (Database) section in the admin panel, I have most of it figured out, but I've ran into an issue. I need to calculate and export the entire customer's order history value.

When I use the below code, it exports the total value, of every order and assigns it to just the first customer in the list.

How can the below code work? With it showing the total value paid PER customer?

Please and thank you!!

This is to export into a customer LAL list for Facebook ads. I'd appreciate if anyone could please help me fix this! It's nearly done!

SELECT c.`email`,  B.phone, c.firstname AS fn, c.lastname AS ln, B.postcode AS zip, B.city AS ct FROM `ps_customer` c
LEFT JOIN ps_address AS B ON (B.id_customer = c.id_customer) 
LEFT JOIN `ps_orders` o ON (c.`id_customer` = o.`id_customer`) LEFT JOIN `ps_order_detail` od ON o.`id_order` = od.`id_order` WHERE o.valid =1

To add to this, I also need it to only list orders which are SHIPPED. Ignore all other statuses. 

Please help, thank you!

Hi,

I am trying to export SQL via PrestaShop's (Database) section in the admin panel, I have most of it figured out, but I've ran into an issue. I need to calculate and export the entire customer's order history value.

When I use the below code, it exports the total value, of every order and assigns it to just the first customer in the list.

How can the below code work? With it showing the total value paid PER customer?

Please and thank you!!

This is to export into a customer LAL list for Facebook ads. I'd appreciate if anyone could please help me fix this! It's nearly done!

select A.email,B.phone,A.firstname AS fn,A.lastname AS ln,B.postcode AS zip,B.city AS ct, ROUND(SUM(IFNULL(O.`total_paid_real`, 0 ) / O.conversion_rate), 2) AS value from ps_customer AS A
LEFT JOIN ps_address AS B ON (B.id_customer = A.id_customer)
LEFT JOIN ps_orders AS O ON (O.id_customer = A.id_customer)
ORDER BY A.email LIMIT 10

To add to this, I also need it to only list orders which are SHIPPED. Ignore all other statuses. 

I also need to export the ISO country code, which is in the ps_country = iso_code table. From the ps_address - id_country order field. 

Please help, thank you!

Hi,

I am trying to export SQL via PrestaShop's (Database) section in the admin panel, I have most of it figured out, but I've ran into an issue. I need to calculate and export the entire customer's order history value.

When I use the below code, it exports the total value, of every order and assigns it to just the first customer in the list.

How can the below code work? With it showing the total value paid PER customer?

Please and thank you!!

This is to export into a customer LAL list for Facebook ads. I'd appreciate if anyone could please help me fix this! It's nearly done!

select A.email,B.phone,A.firstname AS fn,A.lastname AS ln,B.postcode AS zip,B.city AS ct, ROUND(SUM(IFNULL(O.`total_paid_real`, 0 ) / O.conversion_rate), 2) AS value from ps_customer AS A
LEFT JOIN ps_address AS B ON (B.id_customer = A.id_customer)
LEFT JOIN ps_orders AS O ON (O.id_customer = A.id_customer)
ORDER BY A.email LIMIT 10

To add to this, I also need it to only list orders which are SHIPPED. Ignore all other statuses. 

Please help, thank you!