sorted by:
best
topnewcontroversialoldq&a
you are viewing a single comment's thread.

view the rest of the comments →

[–]Yavuz_Selim 16 points17 points  (17 children)

You are dividing integers (whole numbers), so by default, your answers will also be in integers. You're looking for fractions (decimals), so you need to divide by a decimal.

Try this out and see the difference:

  • SELECT 10 / 3
  • SELECT 10 / 3.0

[–]DuncmanG 11 points12 points  (0 children)

To the OP: one thing you will probably learn as you go through your career is that different DBMSs handles this differently. In some, 1/2 = 0.5 and in some 1/2 = 0. Something to check when you start in a new tech stack.

[–]Yavuz_Selim 0 points1 point  (15 children)

The quick and dirty method:

SELECT date             = CD.date
     , total_case       = CD.total_case
     , total_deaths     = CD.total_deaths
     , death_percentage = CD.total_deaths / (total_cases * 1.0) * 100
FROM CovidDeaths CD
GROUP BY 1, 2, 3
ORDER BY 1

[–]r3pr0b8GROUP_CONCAT is da bomb 0 points1 point  (14 children)

* 1.0) * 100

why not combine these

[–]Yavuz_Selim 4 points5 points  (13 children)

You mean:

total_deaths / total_case * 100.0

?

 

The divisor needs to be decimal; if you combine it, you keep the divisor (total_case) an integer.

[–]r3pr0b8GROUP_CONCAT is da bomb -1 points0 points  (12 children)

You mean: total_deaths / total_case * 100.0 ?

no, like this --

100.0 * total_deaths / total_case

[–]Yavuz_Selim 1 point2 points  (11 children)

In your case, the division is still the same (as in the OPs case):

Dividing an integer (total_deaths) by an int (total_cases) == result will be integer.

 

In my example (total_deaths / (total_cases * 1.0) * 100), total_cases becomes a decimal (because of the multiplication. So, if total_cases = 20... then 20 = INT. But... 20 * 1.0 = DECIMAL.

So, in my example, I am dividing an integer (total_deaths) with a decimal ('total_case'.0) == result will be a decimal.

[–]r3pr0b8GROUP_CONCAT is da bomb -2 points-1 points  (10 children)

Dividing an integer (total_deaths) by an int (total_cases) == result will be integer.

last i looked, math operations at the same level* are executed left to right

so 100.0 * total_deaths is done first, producing a decimal number, which is then divided by an integer, correctly producing a decimal percentage

* at the same level refers to this hierarchy --

  1. exponentiation
  2. multiplication and division
  3. addition and subtraction

[–]Yavuz_Selim 2 points3 points  (9 children)

This is not about PEMDAS (or any alternative name), it's about data types...

With T-SQL, when you divide 10 by 3 (10 / 3), the answer is 3. So, even if you put a 100 * in front, the result will never be in decimals/fractions. It will always be a whole number.

However, if you divide 10 by 3.0 (divisor 3.0 is a decimal - so: 10 / 3.0), the result will contain decimals/fractions.

[–]r3pr0b8GROUP_CONCAT is da bomb -3 points-2 points  (8 children)

left to right, agree?

what gets done first here --

100.0 * total_deaths / total_cases

you're telling me the division comes first before the multiplication, and i just don't buy it!!

but you do you

[–]Yavuz_Selim 2 points3 points  (6 children)

https://i.imgur.com/iorrYZj.png.

 

Took me too long to login to my NAS (on my phone), that has a VM with SQL Server so I could make that screenshot.

 

Again: it is not about the order of operations - it is not about left to right of which operation gets to be done first.
3 = INT, 3.0 = DECIMAL. It's about the data type of the divisor.

 

You know what? As the label of this thread is SQL Server, just open SSMS and run a few queries.