I have a magento 2.2.5 based website and what I want to do is for the current month count the number of people who were logged in as a customer and made a purchase for the first time (first time buyers).

The query I am using below does not seem to return the correct # of orders. it is giving me some numbers but they seem way to low compared to what I see in the admin panel.

  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 created_at BETWEEN '2019-01-01' AND '2019-01-28'  AND sfo.customer_id > 0    
                GROUP BY sfo.customer_id
              ) AS T
 GROUP BY num_orders ORDER BY 1

Magento 2 has quite a number of predefined order states. Is it possible your IN statement makes your query too narrow? Perhaps you could try a NOT IN statement that weeds out the orders you don't want to count (for example, cancelled orders).

  • thank you @kookaburra I edited my query as sown in my original post above. I removed the status but now what happens is the query shows me a total of 120 in the customercount column but when i go into Magento admin sales order page and filter all orders in this date range selecting each of the customer groups I get a total of 133 so my query seems to be short by 13 ? – Jayreis Jan 29 '19 at 19:46
  • 1
    Set your maximum created_at value to '2019-01-28 23:59:59'. Right now, by not specifying the timestamp, you're asking your database for all orders created up to 2019-01-28 00:00:00, which is probably where the discrepancy lies. – kookaburra Jan 29 '19 at 20:23

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.