MySQL query sum in Left JoinHow to output MySQL query results in CSV format?Should I use the datetime or timestamp data type in MySQL?How to get a list of user accounts using the command line in MySQL?Join vs. sub-queryWhat's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?Mysql join queryadding SUM() to mySQL JOIN queryHow to reset AUTO_INCREMENT in MySQL?How do I import an SQL file using the command line in MySQL?Mysql query LEFT JOIN unexpected result
Can one guy with a duplicator initiate a nuclear apocalypse?
The relationship of noch nicht and the passive voice
What is polynomial time?
Who are the people reviewing far more papers than they're submitting for review?
Can Brexit be undone in an emergency?
Why are there two bearded faces wearing red hats on my stealth bomber icon?
How do you determine which representation of a function to use for Newton's method?
How often is duct tape used during crewed space missions?
Why are Fuji lenses more expensive than others?
Why do we need to use transistors when building an OR gate?
Is there a connection between IT and Ghostbusters?
Most efficient way to convert from 3.5-4.2V (singe cell LiPo) to stable 3.3V for currents up to 500 mA?
Madrid to London w/ Expired 90/180 days stay as US citizen
Cemented carbide swords - worth it?
Algorithm for competing cells of 0s and 1s
What is the maximum viable speed for a projectile within earth's atmosphere?
Floating Point XOR
Tips for remembering the order of parameters for ln?
EU compensation - fire alarm at the Flight Crew's hotel
Unable to see packet drops on tunnels
I feel like most of my characters are the same, what can I do?
Does Mage Hand give away the caster's position?
How to ask a man to not take up more than one seat on public transport while avoiding conflict?
Fun time! Guess what I am!
MySQL query sum in Left Join
How to output MySQL query results in CSV format?Should I use the datetime or timestamp data type in MySQL?How to get a list of user accounts using the command line in MySQL?Join vs. sub-queryWhat's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?Mysql join queryadding SUM() to mySQL JOIN queryHow to reset AUTO_INCREMENT in MySQL?How do I import an SQL file using the command line in MySQL?Mysql query LEFT JOIN unexpected result
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
The query below is using SUM in the select and joining two tables. The results for the SUM are including amounts from all periods. I suspect that is due to where the SUM is placed.
I think the post here
contains the same issue I am having. I have tried to follow the solution given but cannot seem to get it to work.
Query:
SELECT
Mo911ZipLookup.X_STATE AS StateAbbr,
Mo911ZipLookup.X_CITY AS City,
Mo911ZipLookup.X_COUNTY AS County,
SUM(SourceDataTCX.E911Amount) AS E911Amount,
SourceDataTCX.period AS period
FROM (SourceDataTCX
LEFT JOIN Mo911ZipLookup
ON ((SourceDataTCX.ZipCode = Mo911ZipLookup.X_ZIPCODE)))
WHERE (Mo911ZipLookup.X_STATE = 'MO' AND SourceDataTCX.period = '2019-02-01')
GROUP BY Mo911ZipLookup.X_STATE,
Mo911ZipLookup.X_CITY,
Mo911ZipLookup.X_COUNTY,
SourceDataTCX.period
ORDER BY Mo911ZipLookup.X_STATE, Mo911ZipLookup.X_COUNTY, Mo911ZipLookup.X_CITY
The query should return amounts for just the 2019-02-01 period but it includes amounts from all periods. I think the SUM should be moved into the join. Can someone help me with that?
Edit
The query produces results like:
StateAbbr City County E911Amount period
MO BALLWIN SAINT LOUIS 614.80 2019-02-01
MO ELLISVILLE SAINT LOUIS 614.80 2019-02-01
MO MANCHESTER SAINT LOUIS 614.80 2019-02-01
MO TWIN OAKS SAINT LOUIS 614.80 2019-02-01
MO WILDWOOD SAINT LOUIS 614.80 2019-02-01
MO WINCHESTER SAINT LOUIS 614.80 2019-02-01
The amount for each city total showing as the total for all cities. I think the SUM is being applied before the GROUP BY.
mysql
asked Mar 28 at 14:15
Tom VaughanTom Vaughan
1068 bronze badges
add a comment
|
The query below is using SUM in the select and joining two tables. The results for the SUM are including amounts from all periods. I suspect that is due to where the SUM is placed.
I think the post here
contains the same issue I am having. I have tried to follow the solution given but cannot seem to get it to work.
Query:
SELECT
Mo911ZipLookup.X_STATE AS StateAbbr,
Mo911ZipLookup.X_CITY AS City,
Mo911ZipLookup.X_COUNTY AS County,
SUM(SourceDataTCX.E911Amount) AS E911Amount,
SourceDataTCX.period AS period
FROM (SourceDataTCX
LEFT JOIN Mo911ZipLookup
ON ((SourceDataTCX.ZipCode = Mo911ZipLookup.X_ZIPCODE)))
WHERE (Mo911ZipLookup.X_STATE = 'MO' AND SourceDataTCX.period = '2019-02-01')
GROUP BY Mo911ZipLookup.X_STATE,
Mo911ZipLookup.X_CITY,
Mo911ZipLookup.X_COUNTY,
SourceDataTCX.period
ORDER BY Mo911ZipLookup.X_STATE, Mo911ZipLookup.X_COUNTY, Mo911ZipLookup.X_CITY
The query should return amounts for just the 2019-02-01 period but it includes amounts from all periods. I think the SUM should be moved into the join. Can someone help me with that?
Edit
The query produces results like:
StateAbbr City County E911Amount period
MO BALLWIN SAINT LOUIS 614.80 2019-02-01
MO ELLISVILLE SAINT LOUIS 614.80 2019-02-01
MO MANCHESTER SAINT LOUIS 614.80 2019-02-01
MO TWIN OAKS SAINT LOUIS 614.80 2019-02-01
MO WILDWOOD SAINT LOUIS 614.80 2019-02-01
MO WINCHESTER SAINT LOUIS 614.80 2019-02-01
The amount for each city total showing as the total for all cities. I think the SUM is being applied before the GROUP BY.
mysql
asked Mar 28 at 14:15
Tom VaughanTom Vaughan
1068 bronze badges
add a comment
|
The query below is using SUM in the select and joining two tables. The results for the SUM are including amounts from all periods. I suspect that is due to where the SUM is placed.
I think the post here
contains the same issue I am having. I have tried to follow the solution given but cannot seem to get it to work.
Query:
SELECT
Mo911ZipLookup.X_STATE AS StateAbbr,
Mo911ZipLookup.X_CITY AS City,
Mo911ZipLookup.X_COUNTY AS County,
SUM(SourceDataTCX.E911Amount) AS E911Amount,
SourceDataTCX.period AS period
FROM (SourceDataTCX
LEFT JOIN Mo911ZipLookup
ON ((SourceDataTCX.ZipCode = Mo911ZipLookup.X_ZIPCODE)))
WHERE (Mo911ZipLookup.X_STATE = 'MO' AND SourceDataTCX.period = '2019-02-01')
GROUP BY Mo911ZipLookup.X_STATE,
Mo911ZipLookup.X_CITY,
Mo911ZipLookup.X_COUNTY,
SourceDataTCX.period
ORDER BY Mo911ZipLookup.X_STATE, Mo911ZipLookup.X_COUNTY, Mo911ZipLookup.X_CITY
The query should return amounts for just the 2019-02-01 period but it includes amounts from all periods. I think the SUM should be moved into the join. Can someone help me with that?
Edit
The query produces results like:
StateAbbr City County E911Amount period
MO BALLWIN SAINT LOUIS 614.80 2019-02-01
MO ELLISVILLE SAINT LOUIS 614.80 2019-02-01
MO MANCHESTER SAINT LOUIS 614.80 2019-02-01
MO TWIN OAKS SAINT LOUIS 614.80 2019-02-01
MO WILDWOOD SAINT LOUIS 614.80 2019-02-01
MO WINCHESTER SAINT LOUIS 614.80 2019-02-01
The amount for each city total showing as the total for all cities. I think the SUM is being applied before the GROUP BY.
mysql
asked Mar 28 at 14:15
Tom VaughanTom Vaughan
1068 bronze badges
The query below is using SUM in the select and joining two tables. The results for the SUM are including amounts from all periods. I suspect that is due to where the SUM is placed.
I think the post here
contains the same issue I am having. I have tried to follow the solution given but cannot seem to get it to work.
Query:
SELECT
Mo911ZipLookup.X_STATE AS StateAbbr,
Mo911ZipLookup.X_CITY AS City,
Mo911ZipLookup.X_COUNTY AS County,
SUM(SourceDataTCX.E911Amount) AS E911Amount,
SourceDataTCX.period AS period
FROM (SourceDataTCX
LEFT JOIN Mo911ZipLookup
ON ((SourceDataTCX.ZipCode = Mo911ZipLookup.X_ZIPCODE)))
WHERE (Mo911ZipLookup.X_STATE = 'MO' AND SourceDataTCX.period = '2019-02-01')
GROUP BY Mo911ZipLookup.X_STATE,
Mo911ZipLookup.X_CITY,
Mo911ZipLookup.X_COUNTY,
SourceDataTCX.period
ORDER BY Mo911ZipLookup.X_STATE, Mo911ZipLookup.X_COUNTY, Mo911ZipLookup.X_CITY
The query should return amounts for just the 2019-02-01 period but it includes amounts from all periods. I think the SUM should be moved into the join. Can someone help me with that?
Edit
The query produces results like:
StateAbbr City County E911Amount period
MO BALLWIN SAINT LOUIS 614.80 2019-02-01
MO ELLISVILLE SAINT LOUIS 614.80 2019-02-01
MO MANCHESTER SAINT LOUIS 614.80 2019-02-01
MO TWIN OAKS SAINT LOUIS 614.80 2019-02-01
MO WILDWOOD SAINT LOUIS 614.80 2019-02-01
MO WINCHESTER SAINT LOUIS 614.80 2019-02-01
The amount for each city total showing as the total for all cities. I think the SUM is being applied before the GROUP BY.
mysql
mysql
asked Mar 28 at 14:15
Tom VaughanTom Vaughan
1068 bronze badges
asked Mar 28 at 14:15
Tom VaughanTom Vaughan
1068 bronze badges
edited Mar 28 at 23:42
Tom Vaughan
asked Mar 28 at 14:15
Tom VaughanTom Vaughan
1068 bronze badges
asked Mar 28 at 14:15
Tom VaughanTom Vaughan
1068 bronze badges
asked Mar 28 at 14:15
Tom VaughanTom Vaughan
1068 bronze badges
1068 bronze badges
add a comment
|
add a comment
|
1 Answer
1
active
oldest
votes
Move the filter Mo911ZipLookup.X_STATE = 'MO' to the ON clause:
SELECT Mo911ZipLookup.X_STATE AS StateAbbr,
Mo911ZipLookup.X_CITY AS City,
Mo911ZipLookup.X_COUNTY AS `County,,
z.E911Amount,
z.period AS period FROM
(
SELECT SUM(E911Amount`) AS E911Amount,period AS period, ZipCode
FROM SourceDataTCX
WHERE period = '2019-02-01'
GROUP BY period, ZipCode
) z
LEFT JOIN Mo911ZipLookup ON z.ZipCode = Mo911ZipLookup.X_ZIPCODE
AND Mo911ZipLookup.X_STATE = 'MO'
ORDER BY Mo911ZipLookup.X_STATE, Mo911ZipLookup.X_COUNTY, Mo911ZipLookup.X_CITY
answered Mar 28 at 15:27
isaaceisaace
2,9101 gold badge6 silver badges17 bronze badges
Thanks but that did not work. I think the problem is that the SUM is being calculated before the GROUP BY.
– Tom Vaughan
Mar 28 at 19:19
if it's a 1 to many relationship between the 2 tables then the SourceDataTCX.E911Amount will get duplicated, you would need to sum it in a subquery. Let me know if this is the issue.
– isaace
Mar 28 at 19:35
Yes, I think that may be it. Mo911ZipLookup.X_ZIPCODE is not always a 1 to 1 match.
– Tom Vaughan
Mar 28 at 20:55
I changed the query in my answer. You can give it a try.
– isaace
Mar 28 at 21:09
add a comment
|
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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/4.0/"u003ecc by-sa 4.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
);
);
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
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55399811%2fmysql-query-sum-in-left-join%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Move the filter Mo911ZipLookup.X_STATE = 'MO' to the ON clause:
SELECT Mo911ZipLookup.X_STATE AS StateAbbr,
Mo911ZipLookup.X_CITY AS City,
Mo911ZipLookup.X_COUNTY AS `County,,
z.E911Amount,
z.period AS period FROM
(
SELECT SUM(E911Amount`) AS E911Amount,period AS period, ZipCode
FROM SourceDataTCX
WHERE period = '2019-02-01'
GROUP BY period, ZipCode
) z
LEFT JOIN Mo911ZipLookup ON z.ZipCode = Mo911ZipLookup.X_ZIPCODE
AND Mo911ZipLookup.X_STATE = 'MO'
ORDER BY Mo911ZipLookup.X_STATE, Mo911ZipLookup.X_COUNTY, Mo911ZipLookup.X_CITY
answered Mar 28 at 15:27
isaaceisaace
2,9101 gold badge6 silver badges17 bronze badges
Thanks but that did not work. I think the problem is that the SUM is being calculated before the GROUP BY.
– Tom Vaughan
Mar 28 at 19:19
if it's a 1 to many relationship between the 2 tables then the SourceDataTCX.E911Amount will get duplicated, you would need to sum it in a subquery. Let me know if this is the issue.
– isaace
Mar 28 at 19:35
Yes, I think that may be it. Mo911ZipLookup.X_ZIPCODE is not always a 1 to 1 match.
– Tom Vaughan
Mar 28 at 20:55
I changed the query in my answer. You can give it a try.
– isaace
Mar 28 at 21:09
add a comment
|
Move the filter Mo911ZipLookup.X_STATE = 'MO' to the ON clause:
SELECT Mo911ZipLookup.X_STATE AS StateAbbr,
Mo911ZipLookup.X_CITY AS City,
Mo911ZipLookup.X_COUNTY AS `County,,
z.E911Amount,
z.period AS period FROM
(
SELECT SUM(E911Amount`) AS E911Amount,period AS period, ZipCode
FROM SourceDataTCX
WHERE period = '2019-02-01'
GROUP BY period, ZipCode
) z
LEFT JOIN Mo911ZipLookup ON z.ZipCode = Mo911ZipLookup.X_ZIPCODE
AND Mo911ZipLookup.X_STATE = 'MO'
ORDER BY Mo911ZipLookup.X_STATE, Mo911ZipLookup.X_COUNTY, Mo911ZipLookup.X_CITY
answered Mar 28 at 15:27
isaaceisaace
2,9101 gold badge6 silver badges17 bronze badges
Thanks but that did not work. I think the problem is that the SUM is being calculated before the GROUP BY.
– Tom Vaughan
Mar 28 at 19:19
if it's a 1 to many relationship between the 2 tables then the SourceDataTCX.E911Amount will get duplicated, you would need to sum it in a subquery. Let me know if this is the issue.
– isaace
Mar 28 at 19:35
Yes, I think that may be it. Mo911ZipLookup.X_ZIPCODE is not always a 1 to 1 match.
– Tom Vaughan
Mar 28 at 20:55
I changed the query in my answer. You can give it a try.
– isaace
Mar 28 at 21:09
add a comment
|
Move the filter Mo911ZipLookup.X_STATE = 'MO' to the ON clause:
SELECT Mo911ZipLookup.X_STATE AS StateAbbr,
Mo911ZipLookup.X_CITY AS City,
Mo911ZipLookup.X_COUNTY AS `County,,
z.E911Amount,
z.period AS period FROM
(
SELECT SUM(E911Amount`) AS E911Amount,period AS period, ZipCode
FROM SourceDataTCX
WHERE period = '2019-02-01'
GROUP BY period, ZipCode
) z
LEFT JOIN Mo911ZipLookup ON z.ZipCode = Mo911ZipLookup.X_ZIPCODE
AND Mo911ZipLookup.X_STATE = 'MO'
ORDER BY Mo911ZipLookup.X_STATE, Mo911ZipLookup.X_COUNTY, Mo911ZipLookup.X_CITY
answered Mar 28 at 15:27
isaaceisaace
2,9101 gold badge6 silver badges17 bronze badges
Move the filter Mo911ZipLookup.X_STATE = 'MO' to the ON clause:
SELECT Mo911ZipLookup.X_STATE AS StateAbbr,
Mo911ZipLookup.X_CITY AS City,
Mo911ZipLookup.X_COUNTY AS `County,,
z.E911Amount,
z.period AS period FROM
(
SELECT SUM(E911Amount`) AS E911Amount,period AS period, ZipCode
FROM SourceDataTCX
WHERE period = '2019-02-01'
GROUP BY period, ZipCode
) z
LEFT JOIN Mo911ZipLookup ON z.ZipCode = Mo911ZipLookup.X_ZIPCODE
AND Mo911ZipLookup.X_STATE = 'MO'
ORDER BY Mo911ZipLookup.X_STATE, Mo911ZipLookup.X_COUNTY, Mo911ZipLookup.X_CITY
answered Mar 28 at 15:27
isaaceisaace
2,9101 gold badge6 silver badges17 bronze badges
edited Mar 28 at 21:08
answered Mar 28 at 15:27
isaaceisaace
2,9101 gold badge6 silver badges17 bronze badges
answered Mar 28 at 15:27
isaaceisaace
2,9101 gold badge6 silver badges17 bronze badges
answered Mar 28 at 15:27
isaaceisaace
2,9101 gold badge6 silver badges17 bronze badges
2,9101 gold badge6 silver badges17 bronze badges
Thanks but that did not work. I think the problem is that the SUM is being calculated before the GROUP BY.
– Tom Vaughan
Mar 28 at 19:19
if it's a 1 to many relationship between the 2 tables then the SourceDataTCX.E911Amount will get duplicated, you would need to sum it in a subquery. Let me know if this is the issue.
– isaace
Mar 28 at 19:35
Yes, I think that may be it. Mo911ZipLookup.X_ZIPCODE is not always a 1 to 1 match.
– Tom Vaughan
Mar 28 at 20:55
I changed the query in my answer. You can give it a try.
– isaace
Mar 28 at 21:09
add a comment
|
Thanks but that did not work. I think the problem is that the SUM is being calculated before the GROUP BY.
– Tom Vaughan
Mar 28 at 19:19
if it's a 1 to many relationship between the 2 tables then the SourceDataTCX.E911Amount will get duplicated, you would need to sum it in a subquery. Let me know if this is the issue.
– isaace
Mar 28 at 19:35
Yes, I think that may be it. Mo911ZipLookup.X_ZIPCODE is not always a 1 to 1 match.
– Tom Vaughan
Mar 28 at 20:55
I changed the query in my answer. You can give it a try.
– isaace
Mar 28 at 21:09
Thanks but that did not work. I think the problem is that the SUM is being calculated before the GROUP BY.
– Tom Vaughan
Mar 28 at 19:19
Thanks but that did not work. I think the problem is that the SUM is being calculated before the GROUP BY.
– Tom Vaughan
Mar 28 at 19:19
if it's a 1 to many relationship between the 2 tables then the SourceDataTCX.E911Amount will get duplicated, you would need to sum it in a subquery. Let me know if this is the issue.
– isaace
Mar 28 at 19:35
if it's a 1 to many relationship between the 2 tables then the SourceDataTCX.E911Amount will get duplicated, you would need to sum it in a subquery. Let me know if this is the issue.
– isaace
Mar 28 at 19:35
Yes, I think that may be it. Mo911ZipLookup.X_ZIPCODE is not always a 1 to 1 match.
– Tom Vaughan
Mar 28 at 20:55
Yes, I think that may be it. Mo911ZipLookup.X_ZIPCODE is not always a 1 to 1 match.
– Tom Vaughan
Mar 28 at 20:55
I changed the query in my answer. You can give it a try.
– isaace
Mar 28 at 21:09
I changed the query in my answer. You can give it a try.
– isaace
Mar 28 at 21:09
add a comment
|
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55399811%2fmysql-query-sum-in-left-join%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
