Balance to be displayed like this using Sql Query

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;

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

Hi

  I am getting error - Invalid Column Name RowNo

Thanks

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!