Jump to content

SQL Query to list customers with 2 or more orders?


sampsonzak
 Share

Recommended Posts

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!

Share this post


Link to post
Share on other sites

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

 

  • Thanks 1

Share this post


Link to post
Share on other sites

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 by sampsonzak (see edit history)

Share this post


Link to post
Share on other sites

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 by EvaF (see edit history)
  • Thanks 1

Share this post


Link to post
Share on other sites

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! 

Share this post


Link to post
Share on other sites

  • 1 year later...
  • 2 weeks later...
  • 3 weeks later...

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

As a supplement :D , 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!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More