Modify SQL query to also get number of times ordered

Question

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

Answer 1

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

• callmedpit: I get the following error when I do this: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MIN(created_at) as first_order_date, COUNT (*) as num_of_ord
• Amasty: Please add comma after total_of_orders. Have edited the answer.
• callmedpit: I still get a similar error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) as num_of_orders FROM sales_flat_order GROUP BY customer_emai
• Amasty: removed the space before (, so now it count(*). Thanks!

Answer 2

Try

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

Should always work since create_at is in chronological order, but if you having issue use min().

SELECT DISTINCT
 customer_email,
 customer_firstname,
 customer_lastname,
 count(customer_email) as num_of_order,
 min(created_at) first_order,
 SUM(subtotal_invoiced) AS Total
FROM
 sales_flat_order AS a
GROUP BY customer_email
ORDER BY first_order ASC

• Amasty: Why should we use distinct ? Seems redundant as we use group by customer_email Sorting is slow don the query and should be probably committed if not necessary.