CODEWITHSUNDEEP
sqlsql-server
Essam Mohamed
Essam Mohamed

Reputation: 1

Sum Running value with debit and credit based on cndition

I have this query to running value credit with running value

USE tempdb
GO
DROP TABLE TestTable
CREATE TABLE TestTable (ID INT, CREDIT INT,DEBIT INT, TXT NVARCHAR(MAX))
INSERT INTO TestTable (ID, CREDIT,DEBIT,TXT)
SELECT 1, 10,-20,'A' UNION ALL
SELECT 2, 20,-30,'B' UNION ALL
SELECT 3, 30,0,'C' UNION ALL
SELECT 4, 40,0,'C' UNION ALL
SELECT 5, 50,-30,'B' UNION ALL
SELECT 6, 60,0,'A' UNION ALL
SELECT 7, 70,0,'A'
GO


  SELECT ID,txt, CREDIT,DEBIT
,SUM(CREDIT)  OVER(ORDER BY ID ROWS UNBOUNDED PRECEDING)   AS RunningTotal
FROM TestTable AS T

i need this query to sum based on conditions with debit or credit

if txt = 'A' then Running value + credit
if txt = 'B' then Running value + debit

thanks

Upvotes: 0

Views: 899

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You might just want a conditional in the sum():

SELECT ID, txt, CREDIT, DEBIT,
       SUM(CASE WHEN txt = 'A' THEN CREDIT
                WHEN txt = 'B' THEN DEBIT
                ELSE 0
            END)  OVER (ORDER BY ID) AS RunningTotal
FROM TestTable T;

The windows clause is (unbounded preceding) is unnecessary for the cumulative sum. The else 0 is to handle the initial case if txt is neither A nor B. I assume you would want 0.

Upvotes: 1

Telkarion
Telkarion

Reputation: 431

This works on Oracle :

SELECT SUM(CASE WHEN txt = 'A' THEN CREDIT WHEN txt = 'B' THEN DEBIT END)
FROM TestTable

Upvotes: 1

Related Questions