Total Percentage Not Adding up to 100 (postgresql)

Question

Need your feedback please. I am struggling with the following code to make percentage equal to 100.

SELECT animaltype, size, SUM(total) AS total,
ROUND(( SUM(total) * 100 / SUM( SUM(total)) OVER ()),2) AS percentage
FROM animals
WHERE sponsored_animalid IS NULL
GROUP BY animaltype, size
ORDER BY animaltype, size DESC;

This is the output, which equates to 99.99% hence making the query incorrect.

I need the percentage column to be rounded upto 2 decimal places, but total needs to add up to 100. I dont know what is the bug?

As soon as I edit ROUND( ...,3) - the code adds up to 100. But I need the figures to be rounded up to 2 decimal places strictly.

Here is the output when I round up to 3 decimal places and total is 100:

See https://stackoverflow.com/q/13483430/42346 for discussion of options to address this. — mechanical_meat, Nov 11 '21 at 16:14
Hey, thanks for responding. I looked this one up but I need some elaboration on how to implement Largest Remainder Method in this context. — ramsha chowdhrey, Nov 11 '21 at 16:16
You "dont know what is the bug". There **is no bug**. It is a fallacy to expect imprecise values (rounding is by definition imprecise) with any arbitrary precision to sum to a precise value . What happens if you round the same date to 4 places instead of 3. — Belayer, Nov 13 '21 at 05:18

score 0 · Answer 1 · answered Nov 11 '21 at 16:41

Your SQL statement is syntactically incorrect, but I get what you mean.

That's not a perfect solution, but perhaps you can reduce the average rounding error by using a rounding function that round to the nearest even number rather than away from zero:

CREATE FUNCTION myround(numeric, integer) RETURNS numeric
   LANGUAGE sql IMMUTABLE AS
'SELECT CASE WHEN abs($1 * 10::numeric ^ $2 % 1) = 0.5
            THEN round($1 * 10::numeric ^ $2 / 2, 0)
                 * 2 / 10::numeric ^ $2
            ELSE round($1, $2)
       END';

For a really perfect solution you would have to look at this question.

score 0 · Answer 2 · answered Nov 11 '21 at 22:35

Possible practical solution to your issue is rounding the final aggregate by one decimal place less than the rounding on the previous more granular level

INSERT INTO animals (animaltype, size, total)
VALUES 
('Bird', 'Small', 1615),('Bird', 'Medium', 3130),('Bird', 'Large', 8120),
('Cat', 'Small', 518015),('Cat', 'Medium', 250575),('Cat', 'Large', 439490),
('Dog', 'Small', 336680),('Dog', 'Medium', 942095),('Dog', 'Large', 978115);
        
SELECT
animaltype, 
size,
total,
percentage_exact,
percentage_rd2,
sum(percentage_exact) over ()  as all_perc_exact_sum,
sum(percentage_rd2) over ()  as   all_perc_rd2_sum,
round(sum(percentage_rd2) over ()  ,1) as all_perc_rd2_sum_rd1
FROM
  (
  SELECT  
  animaltype, 
  size,
  total,
  100.0 * total / (SUM(total) over ()) as percentage_exact,  
  round(100.0 * total / (SUM(total) over ()),2)  as percentage_rd2
  FROM animals       
  ) s


| animaltype |   size |  total |     percentage_exact | percentage_rd2 | all_perc_exact_sum | all_perc_rd2_sum | all_perc_rd2_sum_rd1 |
|------------|--------|--------|----------------------|----------------|--------------------|------------------|----------------------|
|       Bird |  Small |   1615 | 0.046436935622305255 |           0.05 |                100 |            99.99 |                  100 |
|       Bird | Medium |   3130 |  0.08999851919369378 |           0.09 |                100 |            99.99 |                  100 |
|       Bird |  Large |   8120 |  0.23347858653443881 |           0.23 |                100 |            99.99 |                  100 |
|        Cat |  Small | 518015 |    14.89475492655632 |          14.89 |                100 |            99.99 |                  100 |
|        Cat | Medium | 250575 |    7.204913401584607 |            7.2 |                100 |            99.99 |                  100 |
|        Cat |  Large | 439490 |   12.636884728573955 |          12.64 |                100 |            99.99 |                  100 |
|        Dog |  Small | 336680 |     9.68073528502646 |           9.68 |                100 |            99.99 |                  100 |
|        Dog | Medium | 942095 |   27.088547904084006 |          27.09 |                100 |            99.99 |                  100 |
|        Dog |  Large | 978115 |   28.124249712824213 |          28.12 |                100 |            99.99 |                  100 |

So here round first by 2 decimal place

round(100.0 * total / (SUM(total) over ()),2)  as percentage_rd2

and then round the aggregate by 1 decimal place

round(sum(percentage_2) over ()  ,1) as   all_perc_rd2_sum_rd1

Total Percentage Not Adding up to 100 (postgresql)

2 Answers2