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! 

6 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 creator has so delightfully enchanted the thought of group by this brilliant blog.
    augmented reality

    ReplyDelete
  4. i am for the first time here. I found this board and I in finding It truly helpful & it helped me out a lot. I hope to present something back and help others such as you helped me. Telangana Ration Card Online Application

    ReplyDelete
  5. Choosing a web based access control system might be the correct move for your company, school or facility. network base access control system installation

    ReplyDelete
  6. Royalty Of The Researchers - Apart from these aspects, the technology transfer office also looks after the royalty sharing between the two organizations involved in the technology transfer process.assemblaggi

    ReplyDelete