0

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

2 Answers 2

2
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
4
  • 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
    – callmedpit
    Sep 2, 2014 at 14:18
  • Please add comma after total_of_orders. Have edited the answer.
    – Amasty
    Sep 2, 2014 at 14:51
  • 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
    – callmedpit
    Sep 2, 2014 at 18:05
  • removed the space before (, so now it count(*). Thanks!
    – Amasty
    Sep 4, 2014 at 11:19
0

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
1
  • 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.
    – Amasty
    Aug 30, 2014 at 13:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.