I get the number of orders by shipping_arrival_date with the following query:

SELECT COUNT(*) as orders_on_day, shipping_arrival_dateenter preformatted text here 
  FROM sales_flat_order
group by shipping_arrival_date
order by shipping_arrival_date asc

How can I modify this query in a way that it shows me a third column with the number of first time customers on this particular shipping date?

I know that within sales_flat_order there is the customer_id which matches with the entity_id in customer_entity, but I somehow need to know if the customer has ordered before.

Does anybody can help here?



Here is a SQL query I just tested which will get you orders for a certain date using the default order columns in Magento 1.9 (shipping_arrival is a custom column so I couldn't use it in this query).

FROM sales_flat_shipment as s
WHERE s.created_at LIKE '%2015-12-28%'
AND s.customer_id != ''
AND (SELECT COUNT(*) FROM sales_flat_order as o WHERE o.customer_id = s.customer_id AND o.status = 'complete' having count(*) < 2)

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.