MySQL Query – How to Get Number of Times Ordered

MySQLqueryreportssql

I am using the following SQL query from http://www.demacmedia.com/magento-commerce/calculating-lifetime-value-of-a-customer-in-magento/ to pull the amount of money each customer has spent with us. I would also like to see the number of orders that were placed and even the date of first order if that's possible. How can I modify the SQL query to get this?

SELECT DISTINCT customer_email, customer_firstname, customer_lastname,
SUM(subtotal_invoiced) AS Total
FROM sales_flat_order AS a
GROUP BY customer_email
ORDER BY SUM(subtotal_invoiced) DESC

Best Answer

SELECT 
    customer_email, customer_firstname, customer_lastname,
    SUM(subtotal_invoiced) AS total_of_orders,
    MIN(created_at) as first_order_date,
    COUNT(*) as num_of_orders
FROM sales_flat_order
GROUP BY customer_email
Related Topic