CODEWITHSUNDEEP
sqlsql-server
UDE_Student
UDE_Student

Reputation: 349

"variable" cumulative value in SQL Server

I have a table that looks like this:

id   count   price
1    100      2
2    50       3
3    10       4

I want to get the price for certain cumulative count values, for instance: when I "need" a count of 120, SQL cumulates the first x rows, and checks if the the cumulative value in the x'th row meets the requirements (>120) and then gives me back the price value of the x'th row.

For 120 I want to get 3 as price, for 159.5 then 4, for 80 a 2 etc.

Is that possible in SQL Server?

Upvotes: 0

Views: 255

Answers (2)

hope_is_grim
hope_is_grim

Reputation: 1934

select top 1 price from
(
    select t1.id, t1.count, SUM(t2.count) as sum, t1.price
    from table_name t1
    inner join table_name t2 on t1.id >= t2.id
    group by t1.id, t1.count, t1.price
) t where t.sum >= 120 order by sum

fiddle

Upvotes: 1

Brian DeMilia
Brian DeMilia

Reputation: 13248

Think you want this:

select   top 1
         [price]
from(
select  [id],
        [count],
        [price],
        sum([count]) over(order by [id]) as run_sum
from    tbl
) x
where    120 <= run_sum
order by run_sum

Fiddle: http://www.sqlfiddle.com/#!6/a1976/5/0

Fiddle example for 159.5, http://www.sqlfiddle.com/#!6/a1976/6/0

Fiddle example for 80, http://www.sqlfiddle.com/#!6/a1976/7/0

Upvotes: 3

Related Questions