Why a simple query works in BIgquery but not PostgreSQL?-CodePudding

I wrote the query below to calculate the death percentage in Postgresql and the result is zero,but same query in Bigquery gets the correct result. Can anyone have idea? Thanks!

SELECT 
location,
MAX(total_cases) AS Cases_total,
MAX(total_deaths) AS Death_total, (MAX(total_deaths)/MAX(total_cases))*100 AS DeathPercentange
FROM covid_deaths
WHERE continent IS NOT NULL 
GROUP BY location
ORDER BY DeathPercentange DESC;

I am not allowed to insert my screenshot so I have the link:

Same query in Bigquery

Query in PostgreSQL

The database looks like this: The preview of the database

CodePudding user response：

You do integer division. The result will always be 0 whenever total_deaths < total_cases (which is most likely your case). What you should do is cast to float or decimal at least one operand, e.g.

(MAX(total_deaths)::decimal / MAX(total_cases))*100 AS DeathPercentange

CodePudding user response：

The result is ok, your operation is between big integers. The result is just an integer.

112/9766 = 0 * 100 = 0

If you want a numeric as result you have to cast your columns as numeric

SELECT 
location,
MAX(total_cases) AS Cases_total,
MAX(total_deaths) AS Death_total, (MAX(total_deaths)::numeric/MAX(total_cases)::numeric)*100 AS DeathPercentange
FROM covid_deaths
WHERE continent IS NOT NULL 
GROUP BY location
ORDER BY DeathPercentange DESC;