Reputation: 47
Here is my real problem
how can i subtract all rows with the same id?
here is my query to subtact
SELECT amount - (SELECT amount
FROM tblmonth WHERE id2='1'
AND type='budget')
FROM tblmonth
WHERE id2='1'
and type='actual'
the problem in this query is, it only can subtract one at a time by using the id2='1'.
please see image below for reference. thank you
Upvotes: 0
Views: 1432
Reputation: 1271241
I don't think there is a reason to use subqueries. The logic might be best expressed as:
select sum(case when type = 'budget' then amount
when type = 'actual' then -amount
else 0
end)
from tblmonth
where id = 1
Upvotes: 2
Reputation: 797
Seems your id is a PK, so use top 1 to make sure the query return only one record. If it should return more than one, you can use SUM() function
SELECT TOP 1 ( SELECT TOP 1 amount FROM tblmonth where type = 'budget' and id='1')
- ( SELECT TOP 1 amount FROM tblmonth where type = 'actual' and id='1')
as Result
from tblmonth
Select Top 1 as your result is return only one record,does need to return the number of table tblmonth record of those result.
Upvotes: 1