1

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
  • You have to place all of your WHERE/AND clauses after any joins. That should solve the problem :) – mtr.web May 2 '18 at 16:57
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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