SQL to get customer data with his orders (total paid & total orders & last)

[Welele]

Hi 

 

I am trying to get this datas, but I obtain duplicate results in ORDERS and TOTAL_PAID. Can anyone help me please? I get:

SELECT count(o.id_order) as ORDERS, sum(o.total_paid) as TOTAL_PAID, max(o.date_add) as LAST_ORDER, 
               c.firstname as NOMBRE, c.lastname as APELLIDOS, c.email, a.address1, a.phone, a.phone_mobile, a.postcode, s.name as STATE
FROM ps_state s, ps_customer c
JOIN ps_orders o on o.id_customer = c.id_customer
JOIN ps_address a on a.id_customer = c.id_customer
WHERE s.id_state = a.id_state
GROUP BY o.id_customer having count(o.id_order)>0
order by 1 desc

Best regards

[Welele]

Solved!

Problem was position of joins. Final query correct is:

 

select C.firstname AS NOMBRE, C.lastname AS APELLIDOS, C.email,

           A.phone AS TLF1, A.phone_mobile AS TLF2,

           S.name AS PROVINCIA,

           count(O.id_order) as PEDIDOS, sum(O.total_paid) as TOTAL_GASTADO, max(O.date_add) as ULTIMO_PEDIDO
from ps_customer AS C
         LEFT JOIN ps_address AS A ON (C.id_customer = A.id_customer) 
         LEFT JOIN ps_state AS S ON (A.id_state = S.id_state)
         LEFT JOIN ps_orders O on O.id_customer = C.id_customer
group by O.id_customer having count(O.id_order)>1
order by 1 DESC

 

Regards