CODEWITHSUNDEEP
sqlsql-serversum
Kohins
Kohins

Reputation: 337

SQL Server sum with a where or having condition

I'm hoping this makes sense as what I'm trying to do is SUM rows based on other columns of existing rows. I have tried a couple different ways and what I hope is now close is what I have here. This is not my full SQL but hopefully this small example will get me on track

SELECT Price,SUM(Item) from table where Price >= Price group by Price

Sample Data
| PRICE | ITEM  |
|-------|-------|
|  1.00 |     5 |
|  2.00 |     9 |
|  3.00 |     2 |

Hopeful Result
| PRICE | ITEM  |
|-------|-------|
|  1.00 |     5 |
|  2.00 |    14 |
|  3.00 |    16 |

The actual result is more or less the sample data which I would expect as I am grouping by Price so it makes sense that it returns the rows like this. I just can't seem to think of away to include Price in my select without having to group or use an aggregate on it. I'm thinking I could maybe do this type of calculation with an inner select but I'm hoping there is a different way as my actual query has a lot of joins which could get messy if I go this route.

Thanks for any help.

Upvotes: 0

Views: 156

Answers (3)

xQbert
xQbert

Reputation: 35323

If you're using SQL server 2012...

Select price, item, sum(item) OVER(order by price rows unbounded preceding) as runningtotal
from sample

http://sqlfiddle.com/#!6/36e9f/1/0

Upvotes: 2

Rahul
Rahul

Reputation: 77866

Probably you are trying to do something like below. use a self join with the same table.

See a DEMO Here 

SELECT t1.Price, SUM(t2.Item)
FROM   table1 t1,
       table1 t2
WHERE t2.Price <= t1.Price
GROUP BY t1.Price
ORDER BY t1.price;

Upvotes: 0

Ruslan
Ruslan

Reputation: 2797

You can accomplish this with a sub-query, but a more efficient way might be to use a CROSS/OUTER APPLY. It depends on your specific data. I provide both methods of doing that below... See which one runs faster based on your specific data.

Sub-query method

SELECT DISTINCT op.Price, (SELECT SUM(ip.Item) FROM table ip WHERE ip.Price <= op.Price) as ITEM FROM table op ORDER BY op.Price ASC

Outer-apply method

SELECT DISTINCT op.Price, a.Items
FROM table op
OUTER APPLY (SELECT SUM(ip.Item) as Items FROM TABLE ip WHERE ip.Price <= op.Price) a
ORDER BY op.Price ASC

Upvotes: 1

Related Questions