PrestaShop Database SQL Help

[sampsonzak]

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!

Edited June 22, 2022 by sampsonzak (see edit history)

[sampsonzak]

I need it to add up the entire order value for the customer. Which I have seen is this code:

 

O.total_paid_real (in ps_orders) divided by O.conversion_rate (in ps_orders)

 

I cannot figure it out !

 

Please help. Thank you very much~