SQL OVER Running Total Select One Record

Question

I am trying to use the OVER Clause to return one row. My code is as follow:

SELECT SUM(Price) OVER (ORDER BY [InvoiceID] ROWS UNBOUNDED PRECEDING) 
FROM dbo.Sales 
WHERE InvoiceID = 3427

I want to return:

InvoiceID Price

3427      15.00

Instead what returns is:

InvoiceID    Price

3427    5.00

3427    10.00

3427     15.00

How do I get just the one row?

Is there a reason you cannot get by with top 1, group by and order by? — kgu87, Oct 16 '13 at 00:41
Well Top 1 , returns 3427 5.00 which is opposite of what I'd like, Group By fails because Price is not in an aggregate function, Order By, really wouldn't work, because negative numbers are possible in this scenario. — Joe Stellato, Oct 16 '13 at 00:47
Could you post sample data and exact requirements for the result ? — kgu87, Oct 16 '13 at 00:50
Get rid of your entire `OVER` clause and you will get one line with total. — PM 77-1, Oct 16 '13 at 01:25
I think you're looking for `SELECT SUM(Price) FROM dbo.Sales WHERE InvoiceID = 3427` if you want to return the invoice Id as well just add InvoiceID to the select and use a GROUP BY InvoiceID. If you're looking for what you appear to be looking for, maybe you're looking for a `MAX(Price)`? — misterManager, Oct 16 '13 at 01:30
and to add to @kgu87, I'm quite sure he meant to use all of those things at same time... like: `SELECT TOP 1 InvoiceID, Price FROM dbo.Sales WHERE InvoiceID = 3427 ORDER BY Price DESC` — misterManager, Oct 16 '13 at 01:32
misterManager, I think I am overthinking this, I was reading this [link](http://www.sqlperformance.com/2012/07/t-sql-queries/running-totals) and said, wow that's what I need to use, because I'm dealing with 2 Million + Rows. Is SUM the most efficient way? — Joe Stellato, Oct 16 '13 at 01:55
@JStellato that is the method you want to use *if* you want all of the individual rows with subtotals along the way. If you only want one row per invoice, with the grand total, it is much simpler... — Aaron Bertrand, Oct 16 '13 at 03:08

score 1 · Answer 1 · edited Oct 16 '13 at 11:55

1

Not sure why you are using an OVER() clause. Seems you simply need:

SELECT InvoiceID, Price = SUM(Price)
  FROM dbo.Sales 
  WHERE InvoiceID = 3427  -- maybe you want more than just this one?
                          -- if so, leave out the WHERE clause
  GROUP BY InvoiceID;

edited Oct 16 '13 at 11:55

ypercubeᵀᴹ

113,259
19
174
235

answered Oct 16 '13 at 03:07

Aaron Bertrand

272,866
37
466
490

score 0 · Answer 2 · answered Oct 16 '13 at 05:26

Actually window specification in the OVER clause has three main parts: partitioning, ordering, and framing. A window frame clause (ROWS BETWEEN AND ) filters a frame, or a subset, of rows from the window partition between the two specified delimiters. In your query, the frame is not specified correctly it should be like between the beginning of the partition (i.e., UNBOUNDED PRECEDING) and the current row (CURRENT ROW). Following query should help:

SELECT SUM(Price) OVER (ORDER BY [InvoiceID] ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) 
FROM dbo.Sales 
WHERE InvoiceID = 3427

SQL OVER Running Total Select One Record

2 Answers2