CODEWITHSUNDEEP
sqlsql-server
alex paolo
alex paolo

Reputation: 47

subtract all rows with the same id?

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

enter image description here

Upvotes: 0

Views: 1432

Answers (2)

Gordon Linoff
Gordon Linoff

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

Kason
Kason

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

Related Questions