How do I calculate percentage as integer in PostgreSQL

Question

I have a CTE (cte_person_count) with the following result:

|  name  | count |
------------------
|  Mike  |   9   |
|  Jane  |   4   |
| Frank  |   3   |
| Brian  |   2   |
|   Ann  |   2   |
| Other  |  15   |

From this cte I want to calculate the percentage of the count compared to the sum of the count as a new third column, and I want the percentage without decimals. So I tried this:

SELECT name, 
       count, 
       ROUND(count*100/(SELECT SUM(count) FROM cte_person_count),0) AS percent
FROM cte_person_count

When I do that I get:

|  name  | count | percent | 
----------------------------
|  Mike  |   9   |    26   |
|  Jane  |   4   |    11   |
| Frank  |   3   |     9   |
| Brian  |   2   |     6   |
|   Ann  |   2   |     6   |
| Other  |  15   |    43   |

The only problem is that the sum of the percent column is 101... It probably has something to do with the ROUND operator. How do I avoid this? The sum of my percent column should always be 100.

Answer 1

If you need an exact 100% sum one trick is get the greatest percent value as a difference 100 - sum(all percent's but the greatest) For example

with p as(
    SELECT name, 
       count, 
       ROUND(count*100./(SELECT SUM(count) FROM cte_person_count),0) AS percent
    FROM cte_person_count
)
select name,
   case(row_number() over(order by percent desc)) when 1 
       then 100 - sum(percent) over(order by percent desc rows between 1 following and unbounded following) 
       else percent end pp
from p