Order By Then Sum() up to x return only those

Question

For the below data I want to order it by AverageOfTotal then take the top ItemNumbers where the sum of the average is up to x.

ItemNumber  AverageOfTotal
item-1  0.0235
item-2  0.0149
item-3  0.0203
item-4  0.0101
item-5  0.0084
item-6  0.0096
item-7  0.0092
item-8  0.0062
item-9  0.0069
item-10 0.0084
item-11 0.0132
item-12 0.0058
item-13 0.0094
item-14 0.0028
item-15 0.0061
item-16 0.0047
item-17 0.0038
item-18 0.0021
item-19 0.004
item-20 0.0083
item-21 0.0048
item-22 0.0058
item-23 0.0153
item-24 0.0025
item-25 0.0022
item-26 0.0086
item-27 0.0076
item-28 0.0097
item-29 0.0009
item-30 0.0042
item-31 0.0099
item-32 0.0036

For example if I wanted only top .1 ItemNumbers it would return

item-3 item-23 item-2 item-11 item-4 item-31

              How do i get this column(sum of avg)

item-1  0.0235  0.0235
item-3  0.0203  0.0438
item-23 0.0153  0.0591
item-2  0.0149  0.074
item-11 0.0132  0.0872
item-4  0.0101  0.0973
item-31 0.0099  0.1072
item-28 0.0097  0.1169
item-6  0.0096  0.1265
item-13 0.0094  0.1359
item-7  0.0092  0.1451
item-26 0.0086  0.1537
item-5  0.0084  0.1621
item-10 0.0084  0.1705
item-20 0.0083  0.1788
item-27 0.0076  0.1864
item-9  0.0069  0.1933
item-8  0.0062  0.1995
item-15 0.0061  0.2056
item-12 0.0058  0.2114
item-22 0.0058  0.2172
item-21 0.0048  0.222
item-16 0.0047  0.2267
item-30 0.0042  0.2309
item-19 0.004   0.2349
item-17 0.0038  0.2387
item-32 0.0036  0.2423
item-14 0.0028  0.2451
item-24 0.0025  0.2476
item-25 0.0022  0.2498
item-18 0.0021  0.2519
item-29 0.0009  0.2528

Goal:

How do I generate that column mentioned above? The key is first I need to order by AverageOfTotal and SUM of row to row values up to a number then return those items.

the items are ordered descending by its `AverageOfTotal`, the "sum" he asks for is the sum of all `AverageOfTotal` of items with a bigger(and maybe equal) `AverageOfTotal` and itself — Patrick Artner, Nov 30 '17 at 16:55

Patrick Artner · Answer 1 · 2017-11-30T17:29:22.050

You might be looking (guesstimate from the numbers you produced as example) for something akin to

Select 
    ItemNumber,  
    AverageOfTotal, 
    (
        select sum(AverageOfTotal) 
        from tbl 
        where AverageOfTotal >= yt.AverageOfTotal 
    ) as summedAvgBiggerEqualThisOne 
from tbl yt
where
    (
        select sum(AverageOfTotal) 
        from tbl 
        where AverageOfTotal >= yt.AverageOfTotal 
    ) < 0.1

order by AverageOfTotal desc

The newer sql-server versions have I think a kind of running_tally function - not played with it yet - that look smarter (see Chris Macks answer ) .

For inspiration, look f.e. here: Calculate a Running Total in SQL Server

DDL:

CREATE TABLE tbl    ( ItemNumber  varchar(7),  AverageOfTotal  decimal(6,6));

INSERT INTO tbl    ( ItemNumber ,  AverageOfTotal ) 
    VALUES
        ('item-1', 0.0235),   ('item-2', 0.0149),   ('item-3', 0.0203),     ('item-4', 0.0101),
        ('item-5', 0.0084),   ('item-6', 0.0096),   ('item-7', 0.0092),     ('item-8', 0.0062),
        ('item-9', 0.0069),   ('item-10', 0.0084),  ('item-11', 0.0132),    ('item-12', 0.0058),
        ('item-13', 0.0094),  ('item-14', 0.0028),  ('item-15', 0.0061),    ('item-16', 0.0047),
        ('item-17', 0.0038),  ('item-18', 0.0021),  ('item-19', 0.004),     ('item-20', 0.0083),
        ('item-21', 0.0048),  ('item-22', 0.0058),  ('item-23', 0.0153),    ('item-24', 0.0025),
        ('item-25', 0.0022),  ('item-26', 0.0086),  ('item-27', 0.0076),    ('item-28', 0.0097),
        ('item-29', 0.0009),  ('item-30', 0.0042),  ('item-31', 0.0099),    ('item-32', 0.0036)
    ;

Result:

ItemNumber  AverageOfTotal  summedAvgBiggerEqualThisOne
item-1     0.0235           0.0235
item-3     0.0203           0.0438
item-23    0.0153           0.0591
item-2     0.0149           0.074
item-11    0.0132           0.0872
item-4     0.0101           0.0973

Chris Mack · Accepted Answer · 2017-11-30T17:01:38.217

1

This will do it:

SELECT
    ItemNumber
    , AverageOfTotal
    , SumOfAverageOfTotal
FROM
    (
        SELECT
            ItemNumber
            , AverageOfTotal
            , SUM(AverageOfTotal) OVER (ORDER BY AverageOfTotal DESC) SumOfAverageOfTotal
        FROM YourTable
    ) Q
WHERE SumOfAverageOfTotal < 0.1 -- or <=

edited Nov 30 '17 at 17:01

answered Nov 30 '17 at 16:49

Chris Mack

5,148
2
12
29

Order By Then Sum() up to x return only those

2 Answers2