Get the Running Total in Oracle

This very frequent needful thing for developers as they need to create so many reports based on this concept.

For this you will have to use one of the window functions in oracle which is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

This would add each and every vale with previous value and give like Running Total.

Query for this,

SELECT PRODUCT_NO,PL_NO, UNRESTRICTED_QTY,

SUM(UNRESTRICTED_QTY) OVER (ORDER BY PRODUCT_NO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RUNNING_TOTAL

FROM PRODUCT_LOCATION

WHERE CLIENT_C=UPPER(‘MSWG’) AND UNRESTRICTED_QTY>0 AND LOCATION_NO=’RECEIPT_BAY‘

row in the result set and adding up the values with currently reading value which is specified by CURRENT ROW up to last record of the record set.

And ordering results by PRODUCT_NO

The result of the above query shown below.





Comments

Post a Comment

Popular posts from this blog

SQL SERVER – TRIM() Function – UDF TRIM()

SQL Server does not have Trim() function. So we can create a own UDF (User Defined Function) function for this since SQL Sever does LTRIM(),RTRIM() functions and we can use this any time. Here I have created a simple Function for this. Create Function Trim(@mText varchar(MAX)) Returns varchar(MAX) AS Begin return LTRIM(RTRIM(@mText)) End You can run this function as shown below here, Select dbo.Trim(' Test ') So this function would return ‘Test’ only as LTRIM() function would cut off the Left side spaces and RTRIM() functiom would cut off the Right side spaces.
Read more
SQL Server/Oracle/MySQL Union query Many time we require results from different tables or different queries. We try one after the other and list the result accordingly. There is a better way to do this by using UNION command or query. We can list records of two different tables by combining two sql queries to one. What is the advantage ? We can as well write the query two times and display one after the other. The point here is we can pick up unique records also. Say we have two tables one is storing name of the football players and other is storing players of baseball team. We want to display a list of players who are member of one of the team at least. Here we can display the name of the players by using UNION command. If John is member of football and baseball team then his name will appear once only. If we want to display all the records then we can use ALL along with the UNION command. Here are two tables with three records each. Please note that there is one common record
Read more