I'm trying to output customer information:
- total sales
- number of orders
- zip code
I am using this to create custom reports using this plugin: https://www.classyllama.com/blog/magento-reports-clean_sqlreports#report_1
I'm getting this error:
This type of clause was previously parsed. (near "LEFT JOIN" at position 970)
SELECT CONCAT('$', FORMAT(SUM(sales_flat_order.`grand_total`), 2)) AS 'Lifetime Sales', COUNT(sales_flat_order.entity_id) AS 'Orders', customer_entity.email AS 'Email', customer_entity_datetime.value AS 'DOB', customer_address_entity_varchar.value as 'PHONE', customer_address_entity_varchar.value as 'FIRSTNAME' FROM `customer_entity` LEFT JOIN sales_flat_order ON customer_entity.entity_id = sales_flat_order.customer_id -- ADD DOB (customer_entity_datetime) LEFT JOIN customer_entity_datetime dob ON customer_entity.entity_id = customer_entity_datetime.entity_id -- ADD TELEPHONE (customer_address_entity_varchar) LEFT JOIN customer_address_entity_varchar phone ON customer_address_entity_varchar.entity_id = customer_entity.entity_id JOIN eav_attribute on eav_attribute.attribute_code="telephone" WHERE customer_address_entity_varchar.attribute_id = eav_attribute.attribute_id -- ADD FISTNAME(customer_address_entity_varchar) LEFT JOIN customer_address_entity_varchar firstname ON customer_address_entity_varchar.entity_id = customer_entity.entity_id JOIN eav_attribute on eav_attribute.attribute_code="firstname" WHERE customer_address_entity_varchar.attribute_id = eav_attribute.attribute_id -- GROUP + SORT GROUP BY customer_entity.entity_id ORDER BY SUM(sales_flat_order.`grand_total`) DESC LIMIT 500
I'm very rusty with SQL, I'm not sure how to do multiple LEFT JOINs. The problem here is that I will need to keep hitting the same table customer_address_entity_varchar for address information.
Here is the full query that I used after @kiatng answered the post. I reordered the list items and removed orders that were canceled or closed. Also added USA / CAD difference so you could separate reports into countries.
SELECT a.firstname as 'FIRST NAME', a.lastname as 'LAST NAME', o.customer_email AS 'Email', o.customer_dob AS 'DOB', a.telephone as 'PHONE', a.country_id as 'Country', a.region as 'State', a.postcode as 'ZIP', FORMAT(SUM(o.`grand_total`), 2) AS 'Lifetime Sales', COUNT(o.entity_id) AS 'Orders' FROM `sales_flat_order` o LEFT JOIN `sales_flat_order_address` a ON a.parent_id = o.entity_id WHERE o.status !="canceled" AND o.status !="closed" AND o.store_currency_code ="USD" -- GROUP + SORT GROUP BY o.customer_id,o.customer_email,o.customer_dob,a.telephone,a.postcode,a.country_id,a.region,a.firstname, a.lastname ORDER BY SUM(o.`grand_total`) DESC LIMIT 500