SQL query: getting customer attributes (M1) - This type of clause was previously parsed

Question

I'm trying to output customer information:

• total sales
• number of orders
• email
• dob
• phone
• firstname
• lastname
• country
• state
• zip code

I am using this to create custom reports using this plugin: https://www.classyllama.com/blog/magento-reports-clean_sqlreports#report_1

Problem:

I'm getting this error:

This type of clause was previously parsed. (near "LEFT JOIN" at position 970)

My Query:

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.

Any ideas?

UPDATE:

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

• mtr.web: You have to place all of your WHERE/AND clauses after any joins. That should solve the problem :)

Answer 1

I'm not any good with SQL too, don't shoot me if the following doesn't do what you want:

SELECT 
 CONCAT('$', FORMAT(SUM(o.`grand_total`), 2)) AS 'Lifetime Sales',
 COUNT(o.entity_id) AS 'Orders',
 o.customer_email AS 'Email',
 o.customer_dob AS 'DOB',
 phone.value as 'PHONE',
 o.customer_firstname as 'FIRSTNAME'
FROM `sales_flat_order` o
-- ADD TELEPHONE (customer_address_entity_varchar)
LEFT JOIN eav_attribute e2
ON e2.attribute_code="telephone"
LEFT JOIN customer_address_entity_varchar phone 
ON phone.entity_id = o.customer_id AND phone.attribute_id = e2.attribute_id AND e2.entity_type_id = phone.entity_type_id
-- GROUP + SORT
GROUP BY o.customer_id,o.customer_email,o.customer_dob,o.customer_firstname,phone.value
ORDER BY SUM(o.`grand_total`) DESC 
LIMIT 500

It's easier to join table sales_flat_order_address:

SELECT 
 CONCAT('$', FORMAT(SUM(o.`grand_total`), 2)) AS 'Lifetime Sales',
 COUNT(o.entity_id) AS 'Orders',
 o.customer_email AS 'Email',
 o.customer_dob AS 'DOB',
 a.telephone as 'PHONE',
 a.postcode as 'ZIP',
 a.country_id as 'Country',
 a.region as 'State',
 a.firstname as 'FIRSTNAME'
FROM `sales_flat_order` o
LEFT JOIN `sales_flat_order_address` a
ON a.parent_id = o.entity_id
-- GROUP + SORT
GROUP BY o.customer_id,o.customer_email,o.customer_dob,a.telephone,a.postcode,a.country_id,a.region,a.firstname
ORDER BY SUM(o.`grand_total`) DESC 
LIMIT 500