Edit History

On 1/11/2023 at 10:23 PM, radu_xc said:

@EvaF Thank you for the code, it was very useful.

As a supplement  , do you think it is possible to extract the information related to the phone number: phone and phone_mobile? I think it is in the PS_address table, I suppose.

Thanks!

something like that (for invoices address )
 

select count(id_order) as cntorders, max(o.date_add) as latest, c.id_customer, c.lastname, c.firstname, 
group_concat(DISTINCT a.phone) as phones, group_concat(DISTINCT a.phone_mobile) as phone_mobiles
from ps_customer c
join ps_orders o on o.id_customer = c.id_customer
join  ps_address a on a.id_address = o.id_address_invoice
where current_state in (5,12,18)
group by o.id_customer having count(o.id_order)>1
order by 1 desc

On 1/11/2023 at 10:23 PM, radu_xc said:

@EvaF Thank you for the code, it was very useful.

As a supplement  , do you think it is possible to extract the information related to the phone number: phone and phone_mobile? I think it is in the PS_address table, I suppose.

Thanks!

something like that (for invoices address )
 

select count(id_order) as cntorders, max(o.date_add) as latest, c.id_customer, c.lastname, c.firstname, group_concat(DISTINCT a.phone) as phones, group_concat(DISTINCT a.phone_mobile) as phone_mobiles
from ps_customer c
join ps_orders o on o.id_customer = c.id_customer
join  ps_address a on a.id_address = o.id_address_invoice
where current_state in (5,12,18)
group by o.id_customer having count(o.id_order)>1
order by 1 desc