Distribute values equally in count and sum to a set of users I have a table with 100 float values (say AMOUNT) that need to be equally divided among 5 users (say UID 1 to 5). The distribution must be such that each user gets approximately the same total count and value of AMOUNT.

Say, I have a sample table with 20 Amounts:

164548.65, 148410.72, 131395.33, 130219.97, 128593.28, 
124539.92, 103958.45, 103671.87, 100210.36,  99645.42,
 98848.25,  97764.84,  97577.03,  90067.98,  87838.22, 
 86730.85,  85508.00,  83481.78,  82886.95,  78588.79


This needs to be divided among 5 codes (numbered 1-5) in such a manner that each code gets 4 Amounts with a net worth of approx 424,000.

I am using SQL Server.

The Amounts are Linked to Invoice numbers. So the table has Invoice_Number,Amount. I need to add a column where the USER_ID (1-4) can be plugged in. So if there are 20 Invoices and 4 users, each user gets assigned with 5 Invoices and the sum of Invoice Amounts for each user must approximately be the same.
Do we have any idea of the distribution of the values ?

A super basic way of handling that, assuming the distribution of the different values of amount are more or less uniform could be split values into N / number_of_values parts (using ntile() function and ordering by amount)

So something like :

select  amount, 
        row_number() over(partition by id order by amount) as id
from (
    select amount, ntile(4) over(order by amount) as id
    from test
    )t
order by 2,1


With the sample data you gave, we get sum 394665.71, 403385.26, 416126.01, 435642.01, 474667.67

Not exactly what you expected but not so far. 

A less super basic way could be trying to split in a different way : after the ntile statement, we have 4 buckets of 5 values, the highest values in bucket #1, the lowest in #2.


Let add the highest value of bucket #1 with the lowest of #2, with
the highest of #3, with the lowest of #4.
And the second highest value of bucket #1 with the second lowest of #2, with the second highest of #3, with the second lowest of #4.
Etc...


So : 

select  amount, 
        case 
            when id % 2 != 0 then row_number() over(partition by id order by amount) 
            else row_number() over(partition by id order by amount desc) 
        end as uid
from (
    select amount, ntile(4) over(order by amount) as id
    from test
    ) t
order by 2,1


With sum 427702.27, 409754.4, 416126.01, 429272.87, 441631.11

For something more accurate, this is more for statistician experts I think.