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! 

3 comments:

  1. This comment has been removed by a blog administrator.

    ReplyDelete
  2. This comment has been removed by a blog administrator.

    ReplyDelete
  3. The last complain mentioned is that some teachers require students to comment on online chat rooms weekly but that they do not monitor the outcome or never make reference to the discussion in class.
    read more

    ReplyDelete