[SOLVED] how to display two digits after decimal point in a calculated column in SQL?

Issue

 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?

Solution

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

Answered By – Ergest Basha

Answer Checked By – Marie Seifert (BugsFixing Admin)