select *,
(TotalDeaths/TotalCases)*100 as PercentDeath,
(TotalDeaths/population)*100 as DeathPercentPopulation
from ( select location,
population,
sum(newcases) as TotalCases,
sum(newdeaths) as TotalDeaths
from deaths
where continent is not null
group by location
) as newtable
order by DeathPercentPopulation desc
There several digits after decimal in "PercentDeath" and "DeathPercentPopulation" column. However I just want to display two digit after decimal. How can I do that without nesting this query again?
CodePudding user response:
You could use TRUNCATE
TRUNCATE(X,D)
Returns the number X, truncated to D decimal places. If D is 0, the result has no decimal point or fractional part. D can be negative to cause D digits left of the decimal point of the value X to become zero.
Something like this:
select *,
TRUNCATE((TotalDeaths/TotalCases)*100,2) as PercentDeath,
TRUNCATE((TotalDeaths/population)*100,2) as DeathPercentPopulation
from ( select location,
population,
sum(newcases) as TotalCases,
sum(newdeaths) as TotalDeaths
from deaths
where continent is not null
group by location
) as newtable
order by DeathPercentPopulation desc
Or you could use ROUND
function.
The ROUND() function in MySQL is used to round a number to a specified number of decimal places. If no specified number of decimal places is provided for round off, it rounds off the number to the nearest integer.
Syntax :
ROUND(X, D) Parameter : This method accepts two parameters in the syntax, as mentioned above and described below –
X : The number which to be rounded. D : Number of decimal places up to which the given number is to be rounded. It is optional. If not given it round off the number to the closest integer. If it is negative, then the number is rounded to the left side of the decimal point.
select *,
ROUND((TotalDeaths/TotalCases)*100,2) as PercentDeath,
ROUND((TotalDeaths/population)*100,2) as DeathPercentPopulation
from ( select location,
population,
sum(newcases) as TotalCases,
sum(newdeaths) as TotalDeaths
from deaths
where continent is not null
group by location
) as newtable
order by DeathPercentPopulation desc
CodePudding user response:
You can use the ROUND() function : https://docs.microsoft.com/fr-fr/sql/t-sql/functions/round-transact-sql?view=sql-server-ver15