Total Percentage Not Adding up to 100 (postgresql)-CodePudding

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:

CodePudding user response：

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.

CodePudding user response：

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