This Question is related to SQL

Aniket Narvankar

1y880SQL Server

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Muhammad Imran Ansari1y ago
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;
```
0
Sreenath Kappoor1y ago
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;
0

Amit Mohanty1y ago

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;