0

Very new and learning SQL. Trying to calculate a percentage from two columns as such:

Select (total_deaths/total_cases)*100 AS death_percentage
From covid_deaths

I’m getting the column but it’s showing as an Integer and all values are zero.

I’ve tried using CAST to make it a decimal but i don’t have the syntax right. Very noob question but seems simple enough. Do I have to declare the numeric type of all calculated columns?

Ryan F
  • 1
  • 1
    does this help - https://stackoverflow.com/questions/34504497/division-not-giving-my-answer-in-postgresql – Stefan Feb 10 '22 at 17:39
  • Ok casting one of the values as opposed to the function, I think that’s what I had wrong. Thanks!! – Ryan F Feb 10 '22 at 17:50
  • This also works, *multiply first then divide*: `100 * total_deaths/total_cases` returns an INT. Use `round(100. * total_deaths/total_cases, 2)` for fractional digits. – dnoeth Feb 10 '22 at 22:35

1 Answers1

0

In addition to the answer linked by Stefan Zivkovik in a comment above, it may be good to handle division by zero. Even if you don't ever anticipate total_cases will be zero, someone may reuse this part of the code (for instance, if total_cases is later broken into subcategories).

You probably also want to ROUND to a certain number of decimal places

SELECT 
  CASE WHEN total_cases > 0 THEN
    ROUND((total_deaths::NUMERIC/total_cases)*100,1)
  END AS death_percentage
FROM covid_deaths

By not specifying an ELSE clause, the column will be null when total_cases is zero. If this doesn't work for your purposes, you could specify another default value (like zero) with ELSE.

Eli Johnson
  • 349
  • 2
  • 10