Balance to be displayed like this using Sql Query

Question

Hi

Sorry for repeating again. I have shown some excel data. Balance to be displayed like this using Sql Query

Row No	Account	Debit	Credit	Balance
1	660293	45000	0	45000
1	660296	3200	0	3200
2	660296	3450	0	6650
3	660296		450	6200
1	700003	0	21092	21092
2	700003	0	13488	34580
3	700003	0	20659	55239
4	700003	16845		38394
5	700003	0	5134	43528

Thaks

Jignesh Kumar · Answer

Hello Ramco, Please use this one, You have used 'RowNo', it should be replaced with "RowNo" SELECT ROW_NUMBER() OVER (PARTITION BY Account ORDER BY A."Account") AS "RowNo", A."Account", A."Debit", A."Credit", SUM(A."Debit" - A."Credit") OVER(PARTITION BY Account ORDER BY "RowNo") AS Balance FROM Masters A;

Ramco Ramco · Answer

Hi I am trying like below but getting error - Windowed functions and NEXT VALUE FOR functions do not support constants as ORDER BY clause expressions. SELECT ROW_NUMBER() OVER (PARTITION BY Account ORDER BY A."Account") AS "RowNo", A."Account" AS "Account",(A."Debit") AS "Debit", (A."Credit") AS "Credit", SUM(A."Debit" - A."Credit") OVER(PARTITION BY Account ORDER BY 'RowNo') AS Balance From Masters A Thanks

Ramco Ramco · Answer

Hi I am getting error - Invalid Column Name RowNo Thanks

Sophia Carter · Answer

Hi there! I see you're looking to understand how to calculate balances based on the Excel data provided using SQL queries. The process involves a cumulative calculation considering both Debit and Credit transactions for each account. To achieve this calculation in SQL, you would typically use a subquery or a window function to compute the running total of Debits minus Credits per account. Below is a simplified example of how you can calculate the balance using SQL: SELECT Account, Debit, Credit, SUM(Debit - Credit) OVER(PARTITION BY Account ORDER BY RowNo) AS Balance FROM YourTableName In this SQL query: - Replace `YourTableName` with the actual table name where your data is stored. - The `PARTITION BY Account` clause ensures that the running total is calculated for each account separately. - `SUM(Debit - Credit) OVER(ORDER BY RowNo)` computes the running balance by subtracting Credits from Debits. By executing this query on your SQL database with appropriate adjustments to match your table structure, you should be able to retrieve the balances similar to the Excel data you provided. If you have any specific SQL database system in mind (e.g., MySQL, PostgreSQL, SQL Server), let me know so I can tailor the query to that particular system. Feel free to reach out if you have further questions or need more clarification on this topic!