Subtract value from same column in SQL Server

smile
 
on Aug 10, 2022 10:48 PM
579 Views

Subtract value from same one column in sql

tblTransaction

ID     Type       Amount    

1       Payment     2000

2       Receipt       1000

3       Payment     3000

4       Payment     2000

5       Receipt       1000

Required output from above table

Payment - Receipt = Balance

7000- 2000 = 5000

Download FREE API for Word, Excel and PDF in ASP.Net: Download
PrinceG
 
on Aug 11, 2022 02:37 AM
on Aug 11, 2022 04:08 AM

Hi smile,

Please refer below sample query.

SQL

CREATE TABLE [Students]
(
	[ID] [int] NOT NULL,
	[Type] [varchar](50) NULL,
	[Amount] [int] NULL
)
GO
INSERT [Students] ([ID], [Type], [Amount]) VALUES (1, N'Payment', 2000)
INSERT [Students] ([ID], [Type], [Amount]) VALUES (2, N'Receipt', 1000)
INSERT [Students] ([ID], [Type], [Amount]) VALUES (3, N'Payment', 3000)
INSERT [Students] ([ID], [Type], [Amount]) VALUES (4, N'Receipt', 2000)
INSERT [Students] ([ID], [Type], [Amount]) VALUES (5, N'Payment', 1000)
GO

SELECT (SELECT SUM(Amount) AS Total 
FROM Students
WHERE [Type] = 'Payment')
-
(SELECT SUM(Amount) AS Total 
FROM Students
WHERE [Type] = 'Receipt') AS 'Balance'

Output

Balance

3000