One approach which is conceptually equivalent to that of @irimias, which consists on doing two things:
First: Sort your amounts (it doesn't really matter if asc or desc):
78588.79
82886.95
83481.78
85508.00
86730.85
87838.22
90067.98
97577.03
97764.84
98848.25
99645.42
100210.36
103671.87
103958.45
124539.92
128593.28
130219.97
131395.33
148410.72
164548.65
Second: Assign to each of those (sorted values), a number from a periodic up-and-down sequence
1
2
3
4
5
5
4
3
2
1
1
2
3
4
5
5
4
3
2
1
Add all the amounts assigned to (1) -> this is the amount for code (1).
Code (1) gets the smallest of the first ten values, and also the biggest of the first ten values, also the smaller of the second group of 10 values, and the biggest one, and so on.
Code (2) will take the second smallest and second biggest, and so on, and so forth.
This requires a bit more of SQL than I'd like, and I've needed a very friendly community help to make it 'SQL Server friendly' (see How to generate a 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, … series).
With that in mind, here comes my proposal:
These are just parameters
DECLARE @n int ;
DECLARE @m int ;
SET @n = 5 ; -- Number of 'codes'
SET @m = 4 ; -- Number of 'repetitions' (20 / 5)
This is the way to generate an "up and down sequence":
-- We need a sequence of numbers, for convenience.
-- There must be more than @n and more than @m
WITH numbers(v) AS
(
SELECT x
FROM (VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9), (10))
AS n(x)
)
-- This nice computation will give us a sequence of the
-- form (1, 2, ... @n, @n, ... 2, 1) x @m
, up_and_down_sequence(i, code) AS
(
SELECT
@n*(up_down+2*m) + n AS i, n*(1-up_down) + ABS(-@n-1+n)*up_down AS code
FROM
(SELECT TOP(@n) v FROM numbers ORDER BY v ASC) n(n)
CROSS JOIN (VALUES(0), (1)) s(up_down)
CROSS JOIN (SELECT TOP(@m) v-1 FROM numbers ORDER BY v ASC) m(m)
)
And finally, we put everything together and sum
-- These are the amounts we're working with
, amounts(a) AS
(
SELECT
*
FROM
(VALUES
(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)
) AS a(a)
)
-- These are the same amounts, ordered
, ordered_amounts AS
(
SELECT
row_number() over (order by a) AS i, a
FROM
amounts
)
-- Here, we join them with our up_and_down_sequence, group and add
SELECT
code, sum(a) AS amount_for_code
FROM
ordered_amounts
JOIN up_and_down_sequence ON up_and_down_sequence.i = ordered_amounts.i
GROUP BY
code
ORDER BY
code ;
The results are:
code amount_for_code
1 441631.11
2 429272.87
3 416126.01
4 409754.40
5 427702.27
... which agrees completely with @irimias approach, which I must confess is way shorter and more elegant; but which took me a while to understand. (I'd better learn how to properly use ntile
;-)