How can I use SUM() OVER()

Question

I can't understand this code's bug

ID      AccountID       Quantity 1          1               10           Sum = 10 2          1               5                = 10 + 5 = 15 3          1               2                = 10 + 5 + 2 = 17 4          2               7                = 7 5          2               3                = 7 + 3 = 10    SELECT ID, AccountID, Quantity,         SUM(Quantity) OVER (PARTITION BY AccountID ) AS TopBorcT,  FROM tCariH 

Answer 1

Seems like you expected the query to return running totals, but it must have given you the same values for both partitions of AccountID.

To obtain running totals with SUM() OVER (), you need to add an ORDER BY sub-clause after PARTITION BY …, like this:

SUM(Quantity) OVER (PARTITION BY AccountID ORDER BY ID) 

But remember, not all database systems support ORDER BY in the OVER clause of a window aggregate function. (For instance, SQL Server didn't support it until the latest version, SQL Server 2012.)

Answer 2

if you are using SQL 2012 you should try

SELECT  ID,          AccountID,          Quantity,          SUM(Quantity) OVER (PARTITION BY AccountID ORDER BY AccountID rows between unbounded preceding and current row ) AS TopBorcT,  FROM tCariH 

if available, better order by date column.