Magento country wise annual sales report directly from the database

There are many extensions out there which you can use to get country wise sales reports. However, you can retrieve much more things if you be little familiar with SQL queries. You can use below queries to get annual sales figures based on countries. 
*Please note that the order values displayed are the subtotals before taxes.Detailed Report

SELECT orders.increment_id as id, orders.state, addresses.firstname, addresses.lastname, addresses.country_id, orders.subtotal as subtotal, orders.created_at
FROM sales_flat_order AS orders
JOIN sales_flat_order_address AS addresses
ON addresses.parent_id=orders.entity_id
WHERE relation_child_real_id IS NULL
AND addresses.address_type=’shipping’
AND YEAR(orders.created_at) = YEAR(CURDATE())

Summery Report

SELECT addresses.country_id, sum(orders.subtotal) as subtotal
FROM sales_flat_order AS orders
JOIN sales_flat_order_address AS addresses
ON addresses.parent_id=orders.entity_id
WHERE relation_child_real_id IS NULL
AND addresses.address_type=’shipping’
AND YEAR(orders.created_at) = YEAR(CURDATE())
GROUP BY addresses.country_id

Hope this helped! 

Like this post? Please share to your friends:
List Of What
Leave a Reply

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: