Count the Number of Customers Who Ordered Once, Twice, etc

Question

I would like a query to return the number of customers who ordered once, twice, etc to the maximum number of times anyone has ordered.

I'm not great at SQL so I've got bits of a query, I just don't know how to piece them together.

I believe this will return the total number of orders per person as num_of_orders:

(SELECT
    customer_id, customer_email,
    COUNT(*) as num_orders
 FROM
    sales_flat_order AS sfo
 WHERE
    sfo.status='complete'
 GROUP BY customer_id) AS num_of_orders

Therefore, this should give the highest number of orders:

MAX(num_of_orders) AS max_num_orders

I then need to count through each row in num_of_orders perhaps using a WHILE loop?

WHILE x != max_num_orders
    COUNT(num_orders = x),
    SET x = x+1
END WHILE

Can anyone help put a query together to achieve this? Thanks!

Answer 1

I've written a module that creates several custom reports including a report for customers who shopped only once.

It's available here if you want: https://github.com/digitalpianism/customreports

The code I use to get the shopped once customers is the following:

$collection = Mage::getResourceModel('reports/order_collection')
    ->addAttributeToSelect('entity_id')
    ->addAttributeToSelect('customer_email')
    ->addAttributeToSelect('customer_firstname')
    ->addAttributeToSelect('customer_lastname')
    ->addAttributeToSelect('created_at');

$collection->getSelect()->group('customer_email')->having('COUNT(customer_email) = 1');

I reckon you can modify this one for twice:

$collection->getSelect()->group('customer_email')->having('COUNT(customer_email) = 2');

And so on

Answer 2

This is what I came up with:

SELECT num_orders AS OrderCount, count(customer_id) as CustomerCount
FROM (
        SELECT
            COUNT(1) AS num_orders,
            sfo.customer_id
        FROM sales_flat_order AS sfo
        WHERE sfo.status = 'complete'    
        GROUP BY sfo.customer_id
    ) AS T
GROUP BY num_orders
ORDER BY 1