0

I'm using PostgreSQL in my data ware house for reporting purposes, and for a certain report I have to show the sum of revenue for each month grouped by country. When i pulled the report the shipping_country_id is showing as null for the last two months (previous months it seems fine - Facing the problem from 2019,April onwards).

When I cross checked there were about 36000 orders having the shipping country ID as null in the database, but in the Magento portal it is showing correctly.

So I'm not sure the table where which I'm taking the data is correct or not. Are there any alternative tables or columns to look for?

Any help will be deeply appreciated ! Thanks in advance.

Note: currently using magento 1.7 (in the process of upgrading it to 2)

Below is the query i'm using,

select shipping_address_id, billing_address_id
FROM sales_flat_order 
WHERE entity_id = 537273;

-- The result of this query is 
--    1074074(shipping_address_id),1074073(billing_address_id)

SELECT *
FROM sales_flat_order_address   
WHERE entity_id = 1074074
OR entity_id = 1074073;

-- The above query is showing nothing.
0

You can join sales_flat_order and sales_flat_order_address and group by the created_at in the first table, then country_id in the second. I used grand_total, which would include shipping and tax, so you might want to look at the other columns in sales_flat_order, but this query has been tested on my own Magento db:

SELECT MONTH(o.created_at), a.country_id, SUM(o.grand_total)
FROM sales_flat_order o
JOIN sales_flat_order_address a
ON o.shipping_address_id = a.entity_id
GROUP BY MONTH(o.created_at), a.country_id;
  • Thanks a lot for your answer! – Sandeep Jun 12 at 11:30
  • @SandeepSalim - Happy to help. If this worked for you, be sure to upvote, so others can benefit from it. – mtr.web Jun 12 at 13:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.