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! 

18 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
  4. What differentiates these databases from those developed in-house on computers is that the airlines, not the agents, control them.oracle dashboards

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. Mail forwarding is not the sole benefits you are attached to while availing the service of a registered office. The facility doesn't help you only in getting you all mails delivered, but also it yield a good corporate image for your company, which leads finally to positive business boost. las vegas virtual address

    ReplyDelete
  7. The blog is like a chocolate for me. I want to have it all the time.
    jio tv

    ReplyDelete
  8. The technology enables various types of work and employment to be decoupled from one another. Firms have greater freedom to locate their economic activities, creating greater competition among regions in infrastructure, labour, capital, and other resource markets.Elektriko paslaugos

    ReplyDelete
  9. Perpetually logical and an outstanding contribution to the world of bloggers.
    Top 10 best thin bezelless monitor

    ReplyDelete
  10. nice sharing love to read your post. i would like to see a post on your blog on psd to cratejoy tips.

    ReplyDelete
  11. The electives and the master's project build on that foundation to help students begin to focus their education on a specific area of technology_management. forbrukeretaten.no

    ReplyDelete
  12. Hey what a brilliant post I have come across and believe me I have been searching out for this similar kind of post for past a week and hardly came across this. Thank you very much and will look for more postings from you. magento2 shipping rates

    ReplyDelete
  13. It is in truth a commendable perfect work of art.
    electric wine opener

    ReplyDelete
  14. tattoo supply It is untrue that white or lighter colored inks are more painful to use.

    ReplyDelete
  15. A debt of gratitude is in order for posting this information. I simply need to tell you that I simply look at your site and I discover it exceptionally fascinating and educational. I can hardly wait to peruse bunches of your posts. Magento plugins by Mageworx

    ReplyDelete
  16. This blog is a first class work, It is indeed a classic work of art.
    consumerisminc.com

    ReplyDelete
  17. Well Said, you have furnished the right information that will be useful to anyone at all time. Thanks for sharing your Ideas.
    Magento course in chennai

    ReplyDelete