SQL distribute values across rows

Question

SQL isn't my forte and I need help. I'm trying to spread a Max value that I am selecting from a table across a number of rows. Let's say I start with a max value of 5 (B.MaxVal = 5) and I have 3 rows to split over (there are 3 instances of the same B.bId).

I want to take the value of 5 and derive a result of each of those instances getting something. If we take the value of 5 and divide it by 3 i will receive a float which is not good. Instead I would like that 5 to get distributed as 2, 2, 1 in the 3 instances of B.bId

select distinct 
    A.aId,  B.bId, B.MaxVal, 
    B.MaxVal / Count(B.bId) OVER(PARTITION BY B.bId) AS 'AVG' 
from 
    CPN
inner join 
    PN on CPN.aId = PN.id
inner join 
    A on A.aId = CPN.aId
left join 
    B on PN.bId = B.bId
where 
    (some search param)
group by 
    A.aId,  B.bId, B.MaxVal

A and B are different tables.

Answer 1

EDIT: Modified to use the FLOOR() function and changed the inequality per your comment.

I would try something like this:

FLOOR(B.MaxVal / COUNT(B.bId) OVER (PARTITION BY B.bId)) 
+ CASE 
    WHEN ROW_NUMBER() OVER (PARTITION BY b.bId ORDER BY b.bId) <= (B.MaxVal % COUNT(B.bId) OVER (PARTITION BY B.bId)) THEN 1 
    ELSE 0 
END as "DISTRIBUTED_AVG"

The first bit is the division you were already doing. ROUND() in SQL Server also serves as the truncate function. ROUND(<expression>,0,1) will truncate the value at the decimal point. Use the FLOOR() function.

The next bit is complicated. Your description is basically that we need to spread out the remainder of the division of the maximum by the count. Well, the remainder of division is the modulo function. So, we know we probably need to use that. That's what (B.MaxVal % COUNT(B.bId) OVER (PARTITION BY B.bId)) is.

Next, we need to know some way how much of the remainder we've used up. Because we're only dealing with the remainder, we know that we never need to give more than one extra item to any value. That also means we'll "consume" the remainder at a rate of 1 per row. So, we need to know which row in the group we're on. To do that, I used the ROW_NUMBER() function. It's partitioned the same as the COUNT() so it will have the same grouping. The only thing you may want to change is the ORDER BY; I just picked something. Basically, we know that when the remainder is equal to or less than the number of the rows we've gone through, then we've got remainder, well, remaining.

I feel like my math is slightly off or I'm missing something, however, because I'm currently kind of tired. I encourage you to look at each of these individually understand what it's doing:

SELECT DISTINCT A.aId,
    B.bId,
    B.MaxVal,
    B.MaxVal / Count(B.bId) OVER (PARTITION BY B.bId) AS 'AVG'
    FLOOR(B.MaxVal / COUNT(B.bId) OVER (PARTITION BY B.bId)), 
    ROW_NUMBER() OVER (PARTITION BY b.bId ORDER BY b.bId), 
    B.MaxVal % COUNT(B.bId) OVER (PARTITION BY B.bId),
    ROUND(B.MaxVal / COUNT(B.bId) OVER (PARTITION BY B.bId),0,1) 
    + CASE 
        WHEN ROW_NUMBER() OVER (PARTITION BY b.bId ORDER BY b.bId) <= (B.MaxVal % COUNT(B.bId) OVER (PARTITION BY B.bId)) THEN 1 
        ELSE 0 
    END as "DISTRIBUTED_AVG"
FROM [...]