This Question is related to SQL

Question

Name Pan Value

A1 abc 5

B1 xyz 10

B2 def 10

C1 ghi 20

C1 klm 20

D1 mno 30

i want to calculate running total for same pan and same name
Name Pan Sum Total

A1 abc 5 5

A1 abc 5 10

B1 xyz 10 10

B2 def 10 10

C1 ghi 20 20

C1 klm 20 20

D1 mno 30 30

D1 mno 30 60

I tried with this query
Select Name,Pan,val,sum(val) over (Partition by Name,Pan order by Name,Pan) as total
but this did not work
it gave total for same pan and name. in total column o/p came as
10
10
10
10
20
20
60
60
please can any one suggest how this can be achieved

Sreenath Kappoor · Answer

Hello Aniket Try this WITH CTE AS ( SELECT * ROW_NUMBER() OVER (PARTITION BY Name Pan ORDER BY (SELECT NULL)) AS RowNum FROM YourTable ) SELECT Name Pan Val SUM(Val) OVER (PARTITION BY Name Pan ORDER BY RowNum ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Total FROM CTE;

Muhammad Imran Ansari · Answer

The issue with your query is that the SUM() window function with PARTITION BY calculates the total for each group (partition) but does not provide a running total within the group. To achieve a running total for each group (based on Name and Pan ) you need to use the ORDER BY clause within the SUM() window function without partitioning. This will ensure that the running total is calculated sequentially for each row within the group. Here’s how you can modify your query to achieve the desired result: SELECT Name Pan Val SUM(Val) OVER (PARTITION BY Name Pan ORDER BY Name Pan ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal FROM YourTableName ORDER BY Name Pan;

Amit Mohanty · Answer

Try this: SELECT Name Pan Val SUM(Val) OVER (PARTITION BY Name Pan ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Total FROM your_table;