This issue has been discussed before, but none of the answers address my specific problem because I am dealing with different where clauses in the inner and outer selects. This query executed just fine under Sybase, but gives the error in the title of this post when executed under SQL Server. The query is complicated, but the general outline of the query is:
select sum ( t.graduates -
( select sum ( t1.graduates )
from table as t1
where t1.id = t.id and t1.group_code not in ('total', 'others' ) ) )
from table as t
where t.group_code = 'total'
The following describes the situation I am trying to resolve:
Is there anyway to rewrite this using derived tables or joins to get the same results?
Update: I created sample data and 3 solutions to my specific problem (2 influenced by sgeddes). The one that I added involves moving the correlated subquery to a derived table in the FROM clause. Thanks for the help guys!
This question is related to
sql
sql-server
aggregate
correlated-subquery
One option is to put the subquery in a LEFT JOIN:
select sum ( t.graduates ) - t1.summedGraduates
from table as t
left join
(
select sum ( graduates ) summedGraduates, id
from table
where group_code not in ('total', 'others' )
group by id
) t1 on t.id = t1.id
where t.group_code = 'total'
group by t1.summedGraduates
Perhaps a better option would be to use SUM with CASE:
select sum(case when group_code = 'total' then graduates end) -
sum(case when group_code not in ('total','others') then graduates end)
from yourtable
Source: Stackoverflow.com