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.

This is less of a coding question, but really depends on your business logic. The moment you use rounding of fractions, you can end up with a different sum. You can avoid that to an extent by using floats/doubles. But it really depends on your presentation layer. In some contexts, a percentage of slightly less or more than 100 can be totally fine. — Hubert Grzeskowiak, Apr 08 '22 at 11:14
So you're telling me that there is no way around it if I don't want to use floats/doubles? — TheStranger, Apr 08 '22 at 11:28
Note that your `round()` is useless. The formula is an integer expression where you already lose precision. It also returns an integer, so the `round()` does nothing — , Apr 08 '22 at 11:34
@Ben What I mean to say is that before anyone can offer a technical solution, you need to say what outcome you are trying to achieve. Let's say you have eight values of 12.5% each. If you round these and add them up, you will end up with 104. If you rendered them as 12 each, you end up with a sum of 96. If you change just some of them, you end up with different percentages for actually equal entries. You need to decide what you want to show before you can make any calculation, in SQL or otherwise. — Hubert Grzeskowiak, Apr 08 '22 at 11:34
@a_horse_with_no_name But if i run it without the ROUND I get over 10 decimals... — TheStranger, Apr 08 '22 at 11:39
@HubertGrzeskowiak Aah, okay that makes sense now that was a good explanation. Thank you! — TheStranger, Apr 08 '22 at 11:53
Then `count` is a `float` or `decimal` not an `integer` as your sample data indicates. https://dbfiddle.uk/?rdbms=postgres_14&fiddle=19aaffb90c85a06a55a9cf0d5a252089 — , Apr 08 '22 at 11:55

score 1 · Accepted Answer · answered Apr 08 '22 at 11:31

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

How do I calculate percentage as integer in PostgreSQL

1 Answers1