I would like to generate Magento native report as follows:

enter image description here

Can anybody provide the SQL query for above table ?

  1. Column Name CustomerGender = customer attribute
  2. Column Name Group = Customer Group native feature
  3. Grand Total = Grand total from Sales order table

Went through plenty of the links but got regular simple joins.

Create Magento Custom Report Module


  • Can you try the query and let me know it's perfect for you! – Vithal Bariya Dec 30 '19 at 5:05
  • Have you tried my code? – Vithal Bariya Dec 30 '19 at 5:56
  • let me give a try..!!! – MageSoftech Dec 30 '19 at 14:18
  • thank for the answer.. i'm able to get the with the result. – MageSoftech Dec 30 '19 at 14:59

Can you try this Query

    eav_attribute_option_value.value AS 'Customer gender',
    customer_group.customer_group_code AS 'Group',
                2)) AS 'Grand total'
        LEFT JOIN
    sales_order ON customer_entity.entity_id = sales_order.customer_id
        LEFT JOIN
    customer_group ON customer_entity.group_id = customer_group.customer_group_id
        LEFT JOIN
    eav_attribute_option_value ON customer_entity.gender = eav_attribute_option_value.option_id
GROUP BY customer_entity.entity_id
ORDER BY SUM(sales_order.`grand_total`) ASC

The final output is enter image description here

| improve this answer | |
  • thanks for the answer – MageSoftech Dec 30 '19 at 14:18
  • Error in query (1055): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'magento2db.eav_attribute_option_value.value' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by – MageSoftech Dec 30 '19 at 14:51

I managed to get the results using below SQL query.

SELECT   eav_attribute_option_value.value as 'Gender', 
                customer_group.customer_group_code as 'Group',
                CONCAT('$', FORMAT(SUM(sales_order.`grand_total`),2)) AS 'Grand total'
FROM `customer_grid_flat`
        customer_group ON customer_grid_flat.group_id = customer_group.customer_group_id
        sales_order ON customer_grid_flat.entity_id = sales_order.customer_id
        eav_attribute_option_value ON customer_grid_flat.gender = eav_attribute_option_value.option_id

WHERE eav_attribute_option_value.value IS NOT NULL
GROUP BY eav_attribute_option_value.value, customer_group_code
| improve this answer | |

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.