0

I have a Magento 2.2.5 based website and I want to get a list of emails of customers who have made more than 2 orders within a specific date range.

I know I can do the below MySQL query to list number times ordered and how many customers. not sure how to maybe alter this MySQL query.

SELECT num_orders AS OrderCount, count(customer_id) as CustomerCount
FROM (
       SELECT
          COUNT(1) AS num_orders,
          sfo.customer_id
          FROM sales_order_grid AS sfo
          WHERE sfo.status='completed_on_sap' AND sfo.created_at BETWEEN '2018-01-01' AND '2018-12-25'
       GROUP BY sfo.customer_id
     ) AS T
GROUP BY num_orders
ORDER BY 1
0

Here's a modified version of a script I used for something similar:

SELECT
    customers.email AS Email,
    COUNT(orders.customer_id) AS OrderCount
    FROM sales_order AS orders
        INNER JOIN customer_grid_flat AS customers
            ON customers.entity_id = orders.customer_id
    WHERE orders.status = 'completed_on_sap'
        AND orders.created_at BETWEEN '2018-01-01' AND '2018-12-25'
    GROUP BY orders.customer_id
    HAVING COUNT(orders.customer_id) > 1;

First you have to group by the customer, then only show customers which have a count higher than 1.

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.