alex paolo alex paolo - 6 months ago 8
SQL Question

subtract two select statement in sql

i got a problem in subtracting a two query value.
here's my sql command

SELECT ( SELECT amount FROM tblmonth where type = 'budget' and id='1') - ( SELECT amount FROM tblmonth where type = 'actual' and id='1' ) from tblmonth


when i run this query i got an error saying. Subquery return more than 1 value.

Please can any one look on this. thanks in advance

Answer

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.