To make a new column from calculated column select in SQL Query-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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