Suppose we have one Table named Account as below
| Act_id | Act_Name | Tr_Type | Amount | Balance |
| 1001 | Xyz | Credit | 20000 | 20000 |
| 1001 | Xyz | Debit | 5000 | 15000 |
| 1002 | Abc | Credit | 15000 | 15000 |
| 1002 | Abc | Debit | 3000 | 12000 |
| 1001 | Xyz | Credit | 5000 | 20000 |
| 1002 | Abc | Credit | 7000 | 19000 |
Then i want to retrieve like following table
| Act_id | Total_Credit | Total_Debit | Balance |
| 1001 | 25000 | 5000 | 20000 |
| 1002 | 22000 | 3000 | 19000 |
To get the like above table follow the query….
SELECT Act_id,Sum(Case when [Tr_Type]='Credit' then Amount end) as 'Total_Credit',
sum(case when [Tr_Type]='Debit' then Amount end) as 'Total_Debit',
(Sum(Case when [Tr_Type]='Credit' then Amount end)-Sum(Case when [Tr_Type]='Debit' then Amount end)) as Balance
FROM [TestExam].[dbo].[Account] GROUP BY Act_id
Thank You.