Cummulative Total.using Sql Query

Question

Hi

I have below data and i want to display Cummulative Total.using Sql Query

G/l Account	Tr. No	Debit	Credit	Cumulative Total
3001	1631519	0	0	0.00
3001	1587055	0	0	0.00
3001	1521307	0	0	0.00
3001	1911332	0	0	0.00
3001	1939638	0	0	0.00
3001	1628491	0	0	0.00
3001	375488	650.16	0	650.16
3001	528264	31,860.00	0	32,510.16
3001	1490428	0	100	32,410.00
3002	375488	100	0	100
3002	528264	200.00	0	300.00
3002	1490428	0	50	250.00

Thanks

Muhammad Imran Ansari · Answer

Hello Ramco, To calculate the Cumulative Total for each G/L Account , you can use the SUM() window function with PARTITION BY . SELECT [G/l Account], [Tr. No], Debit, Credit, SUM(Debit - Credit) OVER (PARTITION BY [G/l Account] ORDER BY [Tr. No] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Cumulative Total] FROM Masters; Good Luck!

Jignesh Kumar · Answer

Hello Ramco, You need to partition by Account and obtain the desired output using the query below. SELECT A."Account" AS "G/L Account", A."TrNo" AS "Tr. No", A."Debit", A."Credit", SUM(A."Debit" - A."Credit") OVER ( PARTITION BY A."Account" ORDER BY A."TrNo" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS "Cumulative Total" FROM Masters A ORDER BY A."Account", A."TrNo";

Emily Foster · Answer

Hi! To calculate the Cumulative Total in SQL based on the data provided, you can use the window function `SUM()` along with `OVER(PARTITION BY)` to accumulate the total based on the G/L Account. Here's a sample SQL query to achieve this: SELECT [G/l Account], [Tr. No], Debit, Credit, SUM(Debit - Credit) OVER(PARTITION BY [G/l Account] ORDER BY [Tr. No]) AS [Cumulative Total] FROM YourTableName Replace `YourTableName` with the actual name of your table. This query will generate the cumulative total based on the G/L Account partition and order it by Transaction Number. If you run this query on your dataset, you should get the cumulative totals as shown in the table. Let me know if you need further clarification or assistance on this!