SQL Query: calculating percentage

Question

If I wanted to find the percentage of people that are from the zip code area of 12345, I would take the number of people with that zip code and divide it from the total number of people… what am I missing in the example query below? I can’t figure out how to display the percentage correctly. It just keeps saying 0%.

select (count(P.PERSON_ID) / (select count(*) from PERSON P)) * 100.00
as “12345 Zip Code Percentage”
from PERSON P
where P.ZIP = '12345'

Thank you.

Answer 1

You're dividing an integer by a larger integer. The result is always going to be zero. Instead, multiply the first count by 100.0 before division. That converts the first count to a floating point number which, when divided by an integer, will give a floating point number and thus the percentage you need.

Answer 2

You're doing an integer division. If the result is lower to 1, it will always display 0 * 100.00, so always 0. If you want an accurate percentage, you need to cast one of the variables as a float.

select (count(P.PERSON_ID) / CAST ((select count(*) from PERSON P) As Float)) * 100.00
as “12345 Zip Code Percentage”
from PERSON P
where P.ZIP = '12345'