Show customers with more than 1 order in MySQL

Submitted by deeperdata - 2 years ago

I've had several instances lately where I needed to debug customer and order data. This query demonstrates how to show customers with more than 1 order from the sales_flat_order table. Tested against Magento CE 1.9.0.1 This technically shows orders where there is a customer ID occurring more than once but I am using it to highlight customers by their unique ID.

// For newer versions of Magento (e.g. CE 1.9.0.1)
SELECT * 
FROM sales_flat_order  
INNER JOIN(  
SELECT customer_id  
FROM sales_flat_order  
GROUP BY customer_id  
HAVING COUNT(customer_id) >1  
)temp ON sales_flat_order.customer_id= temp.customer_id; 

// For older versions of Magento (e.g. when they were still using sales_order instead
// of sales_flat_order)
SELECT * 
FROM sales_order  
INNER JOIN(  
SELECT customer_id  
FROM sales_order  
GROUP BY customer_id  
HAVING COUNT(customer_id) >1  
)temp ON sales_order.customer_id= temp.customer_id; 
affiliate_link
Learn PHP
comments powered by Disqus
Proudly hosted on Digital Ocean