Magento 2 require to create SQL query for the custom report

Question

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

https://github.com/mageplaza/magento-2-reports

• Vithal Bariya: Can you try the query and let me know it's perfect for you!
• Vithal Bariya: Have you tried my code?
• Softec: let me give a try..!!!
• Softec: thank for the answer.. i'm able to get the with the result.

Answer 1

Can you try this Query

SELECT 
 eav_attribute_option_value.value AS 'Customer gender',
 customer_group.customer_group_code AS 'Group',
 CONCAT('$',
 FORMAT(SUM(sales_order.`grand_total`),
 2)) AS 'Grand total'
FROM
 `customer_entity`
 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

• Softec: 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

Answer 2

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`
 LEFT JOIN 
 customer_group ON customer_grid_flat.group_id = customer_group.customer_group_id
 LEFT JOIN
 sales_order ON customer_grid_flat.entity_id = sales_order.customer_id
 LEFT JOIN
 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