get Sum of rows less than the given value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

get Sum of rows less than the given value

Author

Topic

chih
Posting Yak Master

154 Posts

Posted - 2009-10-20 : 20:15:42

Hi All,

here is the sample table
ID price dates
1 10 '2009-1-1'
2 20 '2009-2-1'
3 30 '2009-3-1'
4 40 '2009-4-1'

how can i get subtotal for each row?
ID, price, dates, total
1, 10, '2009-1-1', 10
2, 20, '2009-2-1', 30
3, 30, '2009-3-1', 60
4, 40, '2009-4-1', 100

or is there a wasy i can get all id which total of price is less than 35 order by dates?
the result should be

ID, price, dates, total
1, 10, '2009-1-1', 10
2, 20, '2009-2-1', 30

Thank you in advanced

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-20 : 20:36:17

This will get your running total:
INSERT INTO @Table

SELECT 1 ,10 ,'2009-1-1' UNION ALL
SELECT 2, 20 ,'2009-2-1' UNION ALL
SELECT 3, 30 ,'2009-3-1' UNION ALL
SELECT 4, 40 ,'2009-4-1'

select t1.*,[Total] = sum(t2.price)
from @table t1
inner join
@table t2
on
t1.id >= t2.id
group by
t1.id,t1.price,t1.date
having sum(t2.price) < 35

If you want all the records, just drop the HAVING part

Jim

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-21 : 12:34:41

[code]select t.*
from table t
outer apply(select sum(price) as prev
from table
where dates< t.dates)t1
where t.price + isnull(prev,0)<=35
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources