SQL Query to list customers with 2 or more orders?

[sampsonzak]

Hi,

 

Wondering if any PrestaShop Pro can help me out, I am just looking for a SQL query to list all customers with 2 or more orders for PrestaShop 1.7

 

Could anyone please spare a couple of minutes and help me out? I have the SQL query to list all customers with an order, but I do not know how to change it so it only lists the customers with 2 or more seperate orders.

 

Please and thank you!

[EvaF]

the sql query can look like:
 

select count(id_order) as cntorders, max(o.date_add) as latest, c.id_customer, lastname, firstname
from ps_customer c 
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

 

[sampsonzak]

3 hours ago, EvaF said:

the sql query can look like:
 

select count(id_order) as cntorders, max(o.date_add) as latest, c.id_customer, lastname, firstname
from ps_customer c 
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

 

Thank you very much! It works, however it is still counting orders that have been cancelled/refunded.

 

How could I make this query only count the orders which are marked as "Shipped"? Is this possible?

 

Thank you.

Edited January 13, 2021 by sampsonzak (see edit history)

[EvaF]

it depends on your orderstatuses -

if you enumerate id order state for  "successfull" order

fe

5, 12, 18

then you can write

Quote

select count(id_order) as cntorders, max(o.date_add) as latest, c.id_customer, lastname, firstname
from ps_customer c 
join ps_orders o on o.id_customer = c.id_customer
where current_state in (5,12,18)
group by o.id_customer having count(o.id_order)>1
order by 1 desc

 

or you can follow 'invoice' flag

select count(id_order) as cntorders, max(o.date_add) as latest, c.id_customer, lastname, firstname
from ps_customer c 
join ps_orders o on o.id_customer = c.id_customer
where current_state in (select id_order_state FROM ps_order_state where invoice=1)
group by o.id_customer having count(o.id_order)>1
order by 1 desc

 

Edited January 13, 2021 by EvaF (see edit history)

[sampsonzak]

On 1/13/2021 at 7:54 PM, EvaF said:

it depends on your orderstatuses -

if you enumerate id order state for  "successfull" order

fe

5, 12, 18

then you can write

or you can follow 'invoice' flag

select count(id_order) as cntorders, max(o.date_add) as latest, c.id_customer, lastname, firstname
from ps_customer c 
join ps_orders o on o.id_customer = c.id_customer
where current_state in (select id_order_state FROM ps_order_state where invoice=1)
group by o.id_customer having count(o.id_order)>1
order by 1 desc

 

Thank you very much. I set it to ID 4 which is "Shipped" on my store and it works perfectly.

 

Thank you very much! 

[Nice-side]

ok

Edited December 15, 2022 by Nice-side (see edit history)

[musicmaster]

On 12/15/2022 at 10:30 AM, Nice-side said:

ok

Note that the orders table has also a "valid" field. That is used in the backoffice.

[radu_xc]

@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!

[Nice-side]

merci à tous pour votre aide

[bera_ramazan]

Please goto your admin panel > order

click this menu

[EvaF]

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

 

Edited February 23, 2023 by EvaF (see edit history)