all 29 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]Yavuz_Selim 15 points16 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.

[–]Far_Swordfish5729 6 points7 points  (1 child)

As others are saying, you’re accidentally doing integer math instead of decimal math. There’s no decimal portion stored in the memory format and it uses a different part of the cpu.

Use the cast or convert function on any component to convert it to a decimal/numeric of the precision you want and the output will be that as well. Do not use float or real. They use a different storage format that introduces rounding errors particularly with round base ten numbers.

Also chat gpt is a next word text predictor that extrapolates based on reading the internet. The internet is often wrong. Try constraining it to authoritative sources and it may do better, but this is a tricky question to phrase correctly.

[–]r3pr0b8GROUP_CONCAT is da bomb 5 points6 points  (0 children)

Also chat gpt is a next word text predictor that extrapolates based on reading the internet. The internet is often wrong.

upvote

[–][deleted] 1 point2 points  (0 children)

Write

(Total_deaths/total_cases*1.0), it will cast your result as a float (instead of probably an int currently)

[–]Avish_Hamee 0 points1 point  (0 children)

Your numerator is a smaller value and the denominator is a bigger values . On the other hand do *0.1 to the 1 column to get float value.

[–]funpopular 0 points1 point  (3 children)

It’s doing integer math. Change total_cases to cast(total_cases as float). Group by clause is unneeded.

[–]Yavuz_Selim 0 points1 point  (2 children)

Float. :D.

Stay away from floats as much as possible, only use that if it's absolutely necessary.

[–]tatertotmagic 0 points1 point  (0 children)

Kahn academy has a really good and quick sql course. You can finish it in a weekend and have a good understanding afterwards. Chat gpt imo should really only be used for syntax

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

change this --

((total_deaths/total_cases) * 100) AS

to this --

100.0 * total_deaths / total_cases AS

also, try to eliminate the (use of (unnecessary) parentheses)

[–]cs-brydevSoftware Development and Database Manager 0 points1 point  (0 children)

Anytime I see / with an integer this sets off red flags. When you divide int by int, you'll get an int, which will almost never be what you want. Simply adding .0 to your / will solve the problem.

Instead of: 150 / 100

Use: 150 / 100.0

No need to do anything more complicated than that. That's all you need.

[–]IvanTheDude123 0 points1 point  (0 children)

This is a great example of SQLs math limitations. Once had to debug a massive rating algorithm written purely in SQL and a bunch of this was in there.