How can I use SUM() OVER()

sql sum
serkan picture serkan · Apr 6, 2012 · Viewed 184.1k times · Source

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

Andriy M picture Andriy M · Apr 6, 2012

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.)

Related questions

SUM of grouped COUNT in SQL Query

I have a table with 2 fields: ID Name -- ------- 1 Alpha 2 Beta 3 Beta 4 Beta 5 Charlie 6 Charlie I want to group them by name, with 'count', and a row 'SUM' Name Count ------- ----- Alpha 1 Beta 3 Charlie 2 SUM 6 How would I …

Read More
SQL not a single-group group function

When I run the following SQL statement: SELECT MAX(SUM(TIME)) FROM downloads GROUP BY SSN It returns the maximum sum value of downloads by a customer, however if I try to find the social security number that that max …

Read More
SELECT query with CASE condition and SUM()

I'm currently using these sql statements. My table has the field CPaymentType which contains "Cash" or "Check". I can sum up the amount of payments by executing 2 SQL statements as shown below. In this case, the user won't even notice …

Read More