SQL Server "cannot perform an aggregate function on an expression containing an aggregate or a subquery", but Sybase can
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.idwhere 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