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

User · Answer

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

User · Answer

Query would be like this   SELECT ID  AccountID  Quantity          SUM Quantity  OVER  PARTITION BY AccountID   AS TopBorcT          FROM  Empl ORDER BY AccountID   Partition by works like group by  Here we are grouping by AccountID so sum would be corresponding to AccountID       First first case  AccountID   1   then sum quantity    10   5   2      17  amp  For AccountID   2  then sum Quantity    7 3    10   so result would appear like attached snapshot

User · Answer

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

[sql] How can I use SUM() OVER()

Examples related to sql

Examples related to sum