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.