Calculating rate from two table totals SQL
-1
I am taking calculated totals from two different tables that are related to try and find the rate of Crime to Population.
So here is my code:
SELECT SUM(INCIDENT_BY_REGION.Total) AS TotalCrime,
SUM(REGION.Population) AS TotalPopulation,
(TotalCrime/TotalPopulation) AS Rate
FROM INCIDENT_BY_REGION JOIN REGION
ON INCIDENT_BY_REGION.RegionID = REGION.RegionID;
But I get an error,
Error Code 1054: Unknown Column 'TotalCrime' in 'field list
.
What I want is to find the rate of the population to crime.
Any suggestions?
mysql sql join
edited Nov 14 '18 at 16:02
Eray Balkanli
3,93941943
asked Nov 14 '18 at 15:59
A Cruz
32
add a comment |
-1
I am taking calculated totals from two different tables that are related to try and find the rate of Crime to Population.
So here is my code:
SELECT SUM(INCIDENT_BY_REGION.Total) AS TotalCrime,
SUM(REGION.Population) AS TotalPopulation,
(TotalCrime/TotalPopulation) AS Rate
FROM INCIDENT_BY_REGION JOIN REGION
ON INCIDENT_BY_REGION.RegionID = REGION.RegionID;
But I get an error,
Error Code 1054: Unknown Column 'TotalCrime' in 'field list
.
What I want is to find the rate of the population to crime.
Any suggestions?
mysql sql join
edited Nov 14 '18 at 16:02
Eray Balkanli
3,93941943
asked Nov 14 '18 at 15:59
A Cruz
32
1
Possible duplicate of Using an Alias in SQL Calculations
– Nico Haase
Nov 14 '18 at 16:01
Not a duplicate. That reusing of an Alias answer does not work.
– A Cruz
Nov 14 '18 at 16:36
Can you explain what exactly is not working with the given solutions in the other question?
– Nico Haase
Nov 14 '18 at 16:38
add a comment |
-1
-1
-1
I am taking calculated totals from two different tables that are related to try and find the rate of Crime to Population.
So here is my code:
SELECT SUM(INCIDENT_BY_REGION.Total) AS TotalCrime,
SUM(REGION.Population) AS TotalPopulation,
(TotalCrime/TotalPopulation) AS Rate
FROM INCIDENT_BY_REGION JOIN REGION
ON INCIDENT_BY_REGION.RegionID = REGION.RegionID;
But I get an error,
Error Code 1054: Unknown Column 'TotalCrime' in 'field list
.
What I want is to find the rate of the population to crime.
Any suggestions?
mysql sql join
edited Nov 14 '18 at 16:02
Eray Balkanli
3,93941943
asked Nov 14 '18 at 15:59
A Cruz
32
I am taking calculated totals from two different tables that are related to try and find the rate of Crime to Population.
So here is my code:
SELECT SUM(INCIDENT_BY_REGION.Total) AS TotalCrime,
SUM(REGION.Population) AS TotalPopulation,
(TotalCrime/TotalPopulation) AS Rate
FROM INCIDENT_BY_REGION JOIN REGION
ON INCIDENT_BY_REGION.RegionID = REGION.RegionID;
But I get an error,
Error Code 1054: Unknown Column 'TotalCrime' in 'field list
.
What I want is to find the rate of the population to crime.
Any suggestions?
mysql sql join
mysql sql join
edited Nov 14 '18 at 16:02
Eray Balkanli
3,93941943
asked Nov 14 '18 at 15:59
A Cruz
32
edited Nov 14 '18 at 16:02
Eray Balkanli
3,93941943
asked Nov 14 '18 at 15:59
A Cruz
32
edited Nov 14 '18 at 16:02
Eray Balkanli
3,93941943
edited Nov 14 '18 at 16:02
Eray Balkanli
3,93941943
edited Nov 14 '18 at 16:02
Eray Balkanli
3,93941943
3,93941943
asked Nov 14 '18 at 15:59
A Cruz
32
asked Nov 14 '18 at 15:59
A Cruz
32
asked Nov 14 '18 at 15:59
A Cruz
32
32
1
Possible duplicate of Using an Alias in SQL Calculations
– Nico Haase
Nov 14 '18 at 16:01
Not a duplicate. That reusing of an Alias answer does not work.
– A Cruz
Nov 14 '18 at 16:36
Can you explain what exactly is not working with the given solutions in the other question?
– Nico Haase
Nov 14 '18 at 16:38
add a comment |
1
Possible duplicate of Using an Alias in SQL Calculations
– Nico Haase
Nov 14 '18 at 16:01
Not a duplicate. That reusing of an Alias answer does not work.
– A Cruz
Nov 14 '18 at 16:36
Can you explain what exactly is not working with the given solutions in the other question?
– Nico Haase
Nov 14 '18 at 16:38
1
1
Possible duplicate of Using an Alias in SQL Calculations
– Nico Haase
Nov 14 '18 at 16:01
Possible duplicate of Using an Alias in SQL Calculations
– Nico Haase
Nov 14 '18 at 16:01
Not a duplicate. That reusing of an Alias answer does not work.
– A Cruz
Nov 14 '18 at 16:36
Not a duplicate. That reusing of an Alias answer does not work.
– A Cruz
Nov 14 '18 at 16:36
Can you explain what exactly is not working with the given solutions in the other question?
– Nico Haase
Nov 14 '18 at 16:38
Can you explain what exactly is not working with the given solutions in the other question?
– Nico Haase
Nov 14 '18 at 16:38
add a comment |
1 Answer
1
active
oldest
votes
0
Please try below, you should not use aliases while calculating Rate. Also, it is safer to use case-when expression whereas sum(region.population) = 0 to avoid divide by zero error.:
SELECT SUM(INCIDENT_BY_REGION.Total) AS TotalCrime,
SUM(REGION.Population) AS TotalPopulation,
case when SUM(REGION.Population) > 0 then
SUM(INCIDENT_BY_REGION.Total)/SUM(REGION.Population)
else 0 end AS Rate
FROM INCIDENT_BY_REGION JOIN REGION
ON INCIDENT_BY_REGION.RegionID = REGION.RegionID;
answered Nov 14 '18 at 16:02
Eray Balkanli
3,93941943
Why recalculate theSUM()calls? Consider a derived table or CTE which MySQL 8.++ soon will support.
– Parfait
Nov 14 '18 at 16:24
You are right, better to create a temp table, keep the calculated data there and use this table when you need to calculate rate, but for this example I don't think it will create an important performance issue, so the OP will be found what he is looking for. CTE in mysql will be awesome. @Parfait
– Eray Balkanli
Nov 14 '18 at 16:26
@ErayBalkanli . . . No, Parfait is not right. Perhaps a CTE in MySQL 8.0 would help, but the overhead of materializing the intermediate result would probably be far more expensive than just doing the sums.
– Gordon Linoff
Nov 14 '18 at 20:21
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/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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53304226%2fcalculating-rate-from-two-table-totals-sql%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
0
Please try below, you should not use aliases while calculating Rate. Also, it is safer to use case-when expression whereas sum(region.population) = 0 to avoid divide by zero error.:
SELECT SUM(INCIDENT_BY_REGION.Total) AS TotalCrime,
SUM(REGION.Population) AS TotalPopulation,
case when SUM(REGION.Population) > 0 then
SUM(INCIDENT_BY_REGION.Total)/SUM(REGION.Population)
else 0 end AS Rate
FROM INCIDENT_BY_REGION JOIN REGION
ON INCIDENT_BY_REGION.RegionID = REGION.RegionID;
answered Nov 14 '18 at 16:02
Eray Balkanli
3,93941943
Why recalculate theSUM()calls? Consider a derived table or CTE which MySQL 8.++ soon will support.
– Parfait
Nov 14 '18 at 16:24
You are right, better to create a temp table, keep the calculated data there and use this table when you need to calculate rate, but for this example I don't think it will create an important performance issue, so the OP will be found what he is looking for. CTE in mysql will be awesome. @Parfait
– Eray Balkanli
Nov 14 '18 at 16:26
@ErayBalkanli . . . No, Parfait is not right. Perhaps a CTE in MySQL 8.0 would help, but the overhead of materializing the intermediate result would probably be far more expensive than just doing the sums.
– Gordon Linoff
Nov 14 '18 at 20:21
add a comment |
0
Please try below, you should not use aliases while calculating Rate. Also, it is safer to use case-when expression whereas sum(region.population) = 0 to avoid divide by zero error.:
SELECT SUM(INCIDENT_BY_REGION.Total) AS TotalCrime,
SUM(REGION.Population) AS TotalPopulation,
case when SUM(REGION.Population) > 0 then
SUM(INCIDENT_BY_REGION.Total)/SUM(REGION.Population)
else 0 end AS Rate
FROM INCIDENT_BY_REGION JOIN REGION
ON INCIDENT_BY_REGION.RegionID = REGION.RegionID;
answered Nov 14 '18 at 16:02
Eray Balkanli
3,93941943
Why recalculate theSUM()calls? Consider a derived table or CTE which MySQL 8.++ soon will support.
– Parfait
Nov 14 '18 at 16:24
You are right, better to create a temp table, keep the calculated data there and use this table when you need to calculate rate, but for this example I don't think it will create an important performance issue, so the OP will be found what he is looking for. CTE in mysql will be awesome. @Parfait
– Eray Balkanli
Nov 14 '18 at 16:26
@ErayBalkanli . . . No, Parfait is not right. Perhaps a CTE in MySQL 8.0 would help, but the overhead of materializing the intermediate result would probably be far more expensive than just doing the sums.
– Gordon Linoff
Nov 14 '18 at 20:21
add a comment |
0
0
0
Please try below, you should not use aliases while calculating Rate. Also, it is safer to use case-when expression whereas sum(region.population) = 0 to avoid divide by zero error.:
SELECT SUM(INCIDENT_BY_REGION.Total) AS TotalCrime,
SUM(REGION.Population) AS TotalPopulation,
case when SUM(REGION.Population) > 0 then
SUM(INCIDENT_BY_REGION.Total)/SUM(REGION.Population)
else 0 end AS Rate
FROM INCIDENT_BY_REGION JOIN REGION
ON INCIDENT_BY_REGION.RegionID = REGION.RegionID;
answered Nov 14 '18 at 16:02
Eray Balkanli
3,93941943
Please try below, you should not use aliases while calculating Rate. Also, it is safer to use case-when expression whereas sum(region.population) = 0 to avoid divide by zero error.:
SELECT SUM(INCIDENT_BY_REGION.Total) AS TotalCrime,
SUM(REGION.Population) AS TotalPopulation,
case when SUM(REGION.Population) > 0 then
SUM(INCIDENT_BY_REGION.Total)/SUM(REGION.Population)
else 0 end AS Rate
FROM INCIDENT_BY_REGION JOIN REGION
ON INCIDENT_BY_REGION.RegionID = REGION.RegionID;
answered Nov 14 '18 at 16:02
Eray Balkanli
3,93941943
answered Nov 14 '18 at 16:02
Eray Balkanli
3,93941943
answered Nov 14 '18 at 16:02
Eray Balkanli
3,93941943
answered Nov 14 '18 at 16:02
Eray Balkanli
3,93941943
3,93941943
Why recalculate theSUM()calls? Consider a derived table or CTE which MySQL 8.++ soon will support.
– Parfait
Nov 14 '18 at 16:24
You are right, better to create a temp table, keep the calculated data there and use this table when you need to calculate rate, but for this example I don't think it will create an important performance issue, so the OP will be found what he is looking for. CTE in mysql will be awesome. @Parfait
– Eray Balkanli
Nov 14 '18 at 16:26
@ErayBalkanli . . . No, Parfait is not right. Perhaps a CTE in MySQL 8.0 would help, but the overhead of materializing the intermediate result would probably be far more expensive than just doing the sums.
– Gordon Linoff
Nov 14 '18 at 20:21
add a comment |
Why recalculate theSUM()calls? Consider a derived table or CTE which MySQL 8.++ soon will support.
– Parfait
Nov 14 '18 at 16:24
You are right, better to create a temp table, keep the calculated data there and use this table when you need to calculate rate, but for this example I don't think it will create an important performance issue, so the OP will be found what he is looking for. CTE in mysql will be awesome. @Parfait
– Eray Balkanli
Nov 14 '18 at 16:26
@ErayBalkanli . . . No, Parfait is not right. Perhaps a CTE in MySQL 8.0 would help, but the overhead of materializing the intermediate result would probably be far more expensive than just doing the sums.
– Gordon Linoff
Nov 14 '18 at 20:21
Why recalculate the
SUM() calls? Consider a derived table or CTE which MySQL 8.++ soon will support.– Parfait
Nov 14 '18 at 16:24
Why recalculate the
SUM() calls? Consider a derived table or CTE which MySQL 8.++ soon will support.– Parfait
Nov 14 '18 at 16:24
You are right, better to create a temp table, keep the calculated data there and use this table when you need to calculate rate, but for this example I don't think it will create an important performance issue, so the OP will be found what he is looking for. CTE in mysql will be awesome. @Parfait
– Eray Balkanli
Nov 14 '18 at 16:26
You are right, better to create a temp table, keep the calculated data there and use this table when you need to calculate rate, but for this example I don't think it will create an important performance issue, so the OP will be found what he is looking for. CTE in mysql will be awesome. @Parfait
– Eray Balkanli
Nov 14 '18 at 16:26
@ErayBalkanli . . . No, Parfait is not right. Perhaps a CTE in MySQL 8.0 would help, but the overhead of materializing the intermediate result would probably be far more expensive than just doing the sums.
– Gordon Linoff
Nov 14 '18 at 20:21
@ErayBalkanli . . . No, Parfait is not right. Perhaps a CTE in MySQL 8.0 would help, but the overhead of materializing the intermediate result would probably be far more expensive than just doing the sums.
– Gordon Linoff
Nov 14 '18 at 20:21
add a comment |
draft saved
draft discarded
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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.
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53304226%2fcalculating-rate-from-two-table-totals-sql%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
1
Possible duplicate of Using an Alias in SQL Calculations
– Nico Haase
Nov 14 '18 at 16:01
Not a duplicate. That reusing of an Alias answer does not work.
– A Cruz
Nov 14 '18 at 16:36
Can you explain what exactly is not working with the given solutions in the other question?
– Nico Haase
Nov 14 '18 at 16:38