I need to get customer life time sales data. Both Qty and Amount.
I have tried below Mysql Query it's worked fine.
How can i change this Mysql query in Magento collection format.
Because this data showing in Magento Grid.
select CAST(SUM( t.qty )as UNSIGNED) AS `qty`, SUM( t.grand_total )as grand_total, t.customer_id , t.customer_name, t.customer_email_id, date_format(t.customer_dob, '%d-%m-%Y') as customer_dob from (
SELECT `main_table`.`entity_id`, `main_table`.`customer_id`, `main_table`.`grand_total`, `cusFirstnameTb`.`value` AS `customer_first_name`, `cusLastnameTb`.`value` AS `customer_last_name`, CONCAT(cusFirstnameTb.value, ' ', cusLastnameTb.value) AS `customer_name`, `cusDOBTb`.`value` AS `customer_dob`, `cusEmailTb`.`email` AS `customer_email_id`, `sub`.`qty_canceled`, `sub`.`qty_refunded`, `sub`.`qty_ordered`, CAST(SUM( qty_ordered )as UNSIGNED) AS qty, CAST(SUM( grand_total )as UNSIGNED) AS grand_total1, SUM( grand_total ) AS `grand_total2` FROM `sales_flat_order_grid` AS `main_table`
INNER JOIN `customer_entity_varchar` AS `cusFirstnameTb` ON main_table.customer_id = cusFirstnameTb.entity_id AND cusFirstnameTb.attribute_id = 5 AND cusFirstnameTb.entity_type_id = 1
INNER JOIN `customer_entity_varchar` AS `cusLastnameTb` ON main_table.customer_id = cusLastnameTb.entity_id AND cusLastnameTb.attribute_id = 7 AND cusLastnameTb.entity_type_id = 1
INNER JOIN `customer_entity_datetime` AS `cusDOBTb` ON main_table.customer_id = cusDOBTb.entity_id AND cusDOBTb.attribute_id = 11 AND cusDOBTb.entity_type_id = 1
INNER JOIN `customer_entity` AS `cusEmailTb` ON main_table.customer_id = cusEmailTb.entity_id AND cusEmailTb.entity_type_id = 1
INNER JOIN `sales_flat_order_item` AS `sub` ON main_table.entity_id = sub.order_id
WHERE ( cusDOBTb.value = "2014-08-21" )
GROUP BY `order_id`
ORDER BY `order_id` ASC
) t GROUP BY t.customer_id
This result i get from PhpMyadmin, run above query .
Thanks for your help