Cummulative Total

Question

Hi I have below code and i want to display cummulative totals also. SELECT A."Account" AS "G/l Account",(A."Debit") AS "Debit", (A."Credit") AS "Credit" From Masters A group by A."Account" Thanks

Ramco Ramco · Answer

Hi Imram With same Account it shows same Cummulative Total. IT should be row number wise SELECT A."Account" AS "G/l Account", A."Debit", A."Credit", SUM(A."Debit" - A."Credit") OVER (PARTITION BY A."Account" ORDER BY A."Account" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "Cumulative Total" FROM Masters A ORDER BY A."Account"; Thanks

Muhammad Imran Ansari · Answer

Hello Ramco, To display cumulative totals along with your grouped data, you need to use the SUM() window function instead of GROUP BY . The GROUP BY statement currently aggregates data per account but does not allow cumulative calculations. SELECT A."Account" AS "G/l Account", A."Debit", A."Credit", SUM(A."Debit" - A."Credit") OVER (PARTITION BY A."Account" ORDER BY A."Account" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "Cumulative Total" FROM Masters A ORDER BY A."Account"; Good Luck!

Eliana Blake · Answer

Thank you for sharing your query regarding displaying cumulative totals in SQL. To calculate cumulative totals in SQL, you can use window functions like `SUM()` in conjunction with `OVER()` clause. Here's an example of how you can modify your SQL code to include cumulative totals: SELECT A."Account" AS "G/l Account", A."Debit", A."Credit", SUM(A."Debit" - A."Credit") OVER (ORDER BY A."Account") AS "Cumulative Total" FROM Masters A In this adjusted query, the `SUM(A."Debit" - A."Credit") OVER (ORDER BY A."Account")` expression calculates the cumulative total by summing up the differences between debits and credits in the specified order. By incorporating this modification, you will now have an additional column showing the cumulative total. This can provide valuable insights into the running total of your financial data. If you run this query on your dataset, you should see the cumulative totals alongside the individual debit and credit amounts for each account. I hope this explanation helps you achieve the desired result in displaying cumulative totals in your SQL query. If you have any further questions or need more assistance with this topic or any other, feel free to ask!