SQL query: percent calculation

Question

If I wanted to find the percentage of people from the area of ​​zip code 12345, I would take the number of people with this zip code and divide it by the total number of people ... what am I missing in the example request below? I can’t figure out how to correctly display the percentage. He 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' 

Thanks.

Answer 1

You divide an integer by a larger integer. The result will always be zero. Instead, multiply the first count by 100.0 before dividing. This will convert the first count to a floating point number, which when divided by an integer will give a floating point number and therefore the percentage you need.

Answer 2

You are doing integer division. If the result is less than 1, it will always display 0 * 100.00, therefore always 0. If you want to get the exact percentage, you need to include 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' 

Answer 3

If you want to select the number of decimal points, you can also include ROUND() as follows:

 ,ROUND(CAST(COUNT(var1) AS FLOAT)/COUNT(var2),[# decimals]) AS 'mypercentage' 

Answer 4

Try the following:

 SELECT (P.SubSet * 100.00)/p.Total AS "12345 Zip Code Percentage" FROM ( SELECT COUNT(CASE WHEN ZIP = '12345' THEN 1 ELSE 0 END ) Subset, COUNT(*) Total FROM PERSON ) P 

Answer 5

Try this, I added an additional condition to avoid dividing by error 0:

 select CASE WHEN (select count(*) from PERSON P) > 0 THEN (CAST(count(P.PERSON_ID) As Decimal) / CAST((select count(*) from PERSON P)As Decimal)) * 100.00 ELSE 0 END as "12345 Zip Code Percentage" from PERSON P where P.ZIP = '12345'