'To make a new column from calculated column select in SQL Query

I have a data covid death record on a specific date from 2020 until 2022 in a specific. I want to calculate the death percentage for 2 years for every countries using group by of the country.

I try to make the query as such but the query won't work because using subquery as an expression is not allowed. How do I make this query work? Thank you.

SELECT 
   location, 
   sum(total_deaths) as total_deaths, 
   sum(total_cases) as total_cases, 
   (select SUM(total_deaths) 
    FROM CovidDeaths$ 
    GROUP BY location
   )/
   (select sum(total_cases) 
    FROM CovidDeaths$ 
    GROUP BY location
   ) *100 as DeathPercentage 
FROM CovidDeaths$ 
WHERE NOT (total_deaths is null 
           OR total_cases is null 
           OR continent is null
          ) 
GROUP BY location
ORDER BY 1

*location in the query refers to country

I try to make nested query but the value of death percentage become 0.

SELECT location, total_deaths1, total_cases1, total_deaths1/total_cases1*100 as Death_Percentage

FROM(

SELECT location, sum(total_deaths) as total_deaths1, sum(total_cases) as total_cases1

FROM CovidDeaths$ 

WHERE NOT (total_deaths is null OR total_cases is null OR continent is null) 

GROUP BY location

) as death

ORDER BY 1
sqlsql-server


Solution 1:[1]

Many databases use integer arithmetic when integers are used in division. As there are no integers between 0 and 1, this means that all your values are being rounded down to 0 before mutliplying by 100.

  • 2 / 6 * 100 => 0 * 100 => 0

You could simply change the order of the operations

  • 100 * 2 / 6 => 200 / 6 => 66

Or, implicitly convert the values to a non-integer type

  • 100.0 * 2 / 6 => 200.0 / 6 => 33.3333333

You can also use CAST(), CONVERT(), or other functions to explicitly convert the data-type (perhaps to DECIMAL(18,6) or FLOAT, depending on your needs).

  • Exact syntax depends on which database you are using
  • But you haven't specified which database that is

Solution 2:[2]

You don’t need to calculate the average in a subquery, you can just calculate it in your main query

(SUM(total_deaths)/sum(total_cases))*100

UPDATE

This calculated column is part of your main query which you are grouping by location:

SELECT 
location, 
sum(total_deaths) as total_deaths, 
sum(total_cases) as total_cases, 
(SUM(total_deaths)/sum(total_cases))*100
FROM CovidDeaths$ 
WHERE NOT (total_deaths is null 
       OR total_cases is null 
       OR continent is null
      ) 
GROUP BY location
ORDER BY 1

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2

Related Questions