Calculating rate from two table totals SQL

Question

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?

Possible duplicate of [Using an Alias in SQL Calculations](https://stackoverflow.com/questions/2077475/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

score 0 · Accepted Answer · answered Nov 14 '18 at 16:02

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

7,752
11
48
82

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
@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

Calculating rate from two table totals SQL

1 Answers1