Question 272017 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Distribute Value by Row Percentage in SQL

I have a category, percentage table in SQL (the table itself has been obtained by the following query:

SELECT p.category, 
       sum(p.sales) * 100 / sum(sum(p.sales)) OVER () AS percentage
FROM product p
GROUP BY p.category;
Category Percentage
Books 20.51%
Clocks 9.49%
Pens 60.2%
Desks 9.8%

How can I spread $100 (whole dollars, no cents) across the categories by percentage keeping in mind the GROUP BY and windows functions already in the query.

Intended Solution:

Category Percentage Amount
Books 20.51% 21
Clocks 9.49% 9
Pens 60.2% 60
Desks 9.8% 10

I have been googling but I can’t seem to find a similar scenario.

Tags:

2

Answers


  1. 0

    You could just round() the percentages you already have because they already sum up nicely to a 100 when rounded, but that’s because Books round up and Clocks round down, balancing each others’ rounding errors out. If you lose that balance, you’ll get Amounts summing up to less than a 100 or more than a 100: demo

    SELECT category
    ,p::numeric(5,2) AS percentage
    ,(sum(p%1)over()::int >= row_number()over() )::int + trunc(p) AS amount
FROM ( SELECT category, 
              sum(sales)*100/sum(sum(sales))over() AS p
       FROM product
       GROUP BY category) a;

    This distributes the rounding remainders evenly. You can also round all values until the last one and make the last one catch the remainders. Some banks and insurance companies prefer to set up installments this way:

    SELECT category
      ,percentage::numeric(5,2)
      ,case when lead(percentage)over() is null
            then 100-sum(floor(percentage)::int)over w1 
            else floor(percentage)::int
       end AS amount 
FROM ( SELECT p.category, 
              sum(p.sales)*100/sum(sum(p.sales))over() AS percentage
       FROM product p
       GROUP BY p.category) a
WINDOW w1 as (rows unbounded preceding exclude current row);

    Use of floor() keeps the remainder sum positive to avoid that last one dropping below 0.

    Login or Signup to reply.
  2. 0

    To split $100 across categories by percentage as fairly as possible:

    SELECT category, round(pct, 2) AS percentage
     , trunc(pct) + (row_number() OVER (ORDER BY mod(pct, 1) DESC, category) 
                     <= 100 - sum(trunc(pct)) OVER ())::int AS amount
FROM  (
   SELECT p.category
        , sum(p.sales) * 100 / sum(sum(p.sales)) OVER () AS pct
   FROM   product p
   GROUP  BY p.category
   ) sub;

    fiddle

    The specific difficulty is that rounded percentages can sum up to more or less than $100. So assign the minimum share (= truncated percentage) in a first round, and then add $1 to the highest remainders in order until the $100 are spent.

    If categories tie for a remaining buck (identical percentages), alphabetic sort order serves as tiebreaker in my query. Adjust any way you want.

    The fiddle has more details.

    I based my query on a single table products, as presented in the question. In actuality, there will be at least a second table orders in a 1:n relationship. Adjust accordingly. The principle query is the same.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search