SQLTeam.com | Weblogs | Forums

How to Group By The Name Column


#1

Table Name is Table1
I Will Expect output like below

id month inst name total due_amount

1 jan 2000 NAVEEN 8000 6000
2 feb 2000 NAVEEN 8000 4000
3 march 2000 NAVEEN 8000 2000
4 april 2000 NAVEEN 8000 0
5 JAN 1000 RANJITH 3000 2000
6 feb 1000 RANJITH 3000 1000
7 March 1000 RANJITH 3000 0

SELECT M.*, B.SUM_INST AS TOTAL,SUM_INST - SUM(INST)OVER(ORDER BY ID ) AS DUE_AMOUNT
FROM Table1 AS M
cross apply
(SELECT SUM(A.INST) AS SUM_INST
FROM table1 AS A
) AS B


#2
SELECT M.*
      , B.SUM_INST AS TOTAL
      , SUM_INST - SUM(INST) OVER (
            ORDER BY ID
            ) AS DUE_AMOUNT
FROM Table1 AS M
CROSS APPLY (
      SELECT SUM(A.INST) AS SUM_INST
      FROM table1 AS A
      ) AS B
GROUP BY NAME