Who could explain difference between two calculations


#1
SELECT
	state,
  100.0 * sum(
    CASE 
    	WHEN elevation >= 2000 THEN 1 
    	ELSE 0 
    END
  ) / count(*) as percentage_flight_distance_from_delta
FROM airports
GROUP BY state;

and

SELECT
	state,
  100.0 * (sum(
    CASE 
    	WHEN elevation >= 2000 THEN 1 
    	ELSE 0 
    END
  ) / count(*)) as percentage_flight_distance_from_delta
FROM airports
GROUP BY state;

Thank you


#2

How big is the answer difference?
If it’s small (like < 1) then it’s because of the way .0 is handled, try CONVERT(decimal,insertNumber)

EDIT:

SELECT 100 * (5.0 / 12.0) = 41.6666
SELECT 100.0 * (5.00 / 12.00) = 41.66666

#3

if division is wrapped into brackets it doesn’t work correctly.
it works as (0 OR 1)
so what is the reason?

are there some manuals for this?


#4

That’s because the numbers in the division is handled as Integer values. When the first value is larger than the second, the answer is the closest to the times it goes into the first (15/5=3; 16/5=3, 9/5=1) and when it’s smaller the answer is 0 (5/9=0). The answer is rounded down (floored) to the closest integer.

Example of my precious answer:

SELECT 9/5 = 1
SELECT 9 / CONVERT(decimal,5) = 1.8

#5

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.