Ttdfjt

When to use и or а as “and”?

Create a cube from identical 3D objects

Was planting UN flag on Moon ever discussed?

Is Jesus the last Prophet?

What do you call the action of "describing events as they happen" like sports anchors do?

Entered UK using my now-lost UK passport; can I go to Spain using my US passport?

Was self-modifying code possible using BASIC?

If absolute velocity does not exist, how can we say a rocket accelerates in empty space?

How (un)safe is it to ride barefoot?

If the pressure inside and outside a balloon balance, then why does air leave when it pops?

How do I type a hyphen in iOS 12?

What does this line mean in Zelazny's The Courts of Chaos?

What is the STRONGEST end-of-line knot to use if you want to use a steel-thimble at the end, so that you've got a steel-eyelet at the end of the line?

Realistic, logical way for men with medieval-era weaponry to compete with much larger and physically stronger foes

What is the theme of analysis?

Why did Robert pick unworthy men for the White Cloaks?

How to represent jealousy in a cute way?

What do I need to do, tax-wise, for a sudden windfall?

Is it true that "only photographers care about noise"?

Print "N NE E SE S SW W NW"

How to generate list of *all* available commands and functions?

C++ logging library

Why is it bad to use your whole foot in rock climbing

DateTime.addMonths skips a month (from feb to mar)

Magneto Shipping country ID is shown as NULL in the reports

Shipping methods are only being shown to logged in customerExport from the database brand, quantity and size/color varchar: MySQL & EEblock type=“reports/product_viewed” returning the word “empty”Is there way to send the reports generated by magento (under reports) to a mail?Magento programatically convert quote to order - orders missing in backendAdmin/Manual Order - Why is Adding Items slower after order custom attribute installation?Force DB Schema UpdateUsing numeric only increment_id for orders? READ THIS! Magento 1Configurable Product MSRPMagento 2.1.6 website is working fine but suddenly in one subcategory I'm getting the error bellow

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

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.

magento-1.7 database sales-order reports foreign-key

share|improve this question

edited Jun 5 at 13:24

mtr.web

776516

asked Jun 5 at 7:58

Sandeep SalimSandeep Salim

83

add a comment | 

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.

magento-1.7 database sales-order reports foreign-key

share|improve this question

edited Jun 5 at 13:24

mtr.web

776516

asked Jun 5 at 7:58

Sandeep SalimSandeep Salim

83

add a comment | 

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.

magento-1.7 database sales-order reports foreign-key

share|improve this question

edited Jun 5 at 13:24

mtr.web

776516

asked Jun 5 at 7:58

Sandeep SalimSandeep Salim

83

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.

share|improve this question

edited Jun 5 at 13:24

mtr.web

776516

asked Jun 5 at 7:58

Sandeep SalimSandeep Salim

83

share|improve this question

edited Jun 5 at 13:24

mtr.web

776516

asked Jun 5 at 7:58

Sandeep SalimSandeep Salim

83

edited Jun 5 at 13:24

mtr.web

776516

edited Jun 5 at 13:24

mtr.web

776516

asked Jun 5 at 7:58

Sandeep SalimSandeep Salim

83

asked Jun 5 at 7:58

Sandeep SalimSandeep Salim

83

1 Answer
1

active

oldest

votes

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;

share|improve this answer

answered Jun 5 at 13:21

mtr.webmtr.web

776516

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "479"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fmagento.stackexchange.com%2fquestions%2f277319%2fmagneto-shipping-country-id-is-shown-as-null-in-the-reports%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

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;

share|improve this answer

answered Jun 5 at 13:21

mtr.webmtr.web

776516

add a comment | 

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;

share|improve this answer

answered Jun 5 at 13:21

mtr.webmtr.web

776516

add a comment | 

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;

share|improve this answer

answered Jun 5 at 13:21

mtr.webmtr.web

776516

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;

share|improve this answer

answered Jun 5 at 13:21

mtr.webmtr.web

776516

answered Jun 5 at 13:21

mtr.webmtr.web

776516

answered Jun 5 at 13:21

mtr.webmtr.web

776516