Menu
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 
624
SUM(TotalDue) OVER() AS 'Total Business Sales' This expression produces a grand total across the whole data set. There is no partitioning of the data. This is why every record shows the same value for the “Total Business Sales” column.
You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.
It further calculates sum on those rows using sum(Orderamount) with a partition on CustomerCity ( using OVER(PARTITION BY Customercity ORDER BY OrderAmount DESC). Similarly, we can calculate the cumulative average using the following query with the SQL PARTITION BY clause.
If you need to add a group of numbers in your table you can use the SUM function in SQL. This is the basic syntax: SELECT SUM(column_name) FROM table_name; The SELECT statement in SQL tells the computer to get data from the table.
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.)
58
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.
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
