0

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!

0

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

  • Thanks for your response Raphael. Useful module you have there. What I'm looking for though is a SQL query that'll return a table with 2 cols: num_of_orders and num_of_customers, so it'll show the number of customers who have only ever placed 1 order, those who have placed 2, etc all the way up to the max number of orders ever placed. Is that something you can help with? Thanks! – iDiveDeep Dec 5 '16 at 16:29
0

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
  • how do you get customer name – jibin george Nov 19 at 14:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.