sqloracle-database

Sum subtract with SubQuery with count throw error

I am running Oracle 11G. I need to run a query that SUM and then subtract the COUNT using.

select sum(MyColumn1) -  
(SELECT count(*) FROM MOL.Table2 e WHERE e.MyColumn2=t.Column4)
from mol.Table1 t where t.Column3=SomValue;

But it throws SQL Error [937] [42000]: ORA-00937: not a single-group group function.

Solution

  • CTE helps & is easy to read; an example based on Scott's schema:

    SQL> with
  2  tsum as (select sum(e.sal) csum
  3           from emp e
  4          ),
  5  tcnt as (select count(*)   ccnt
  6           from dept d
  7           where d.deptno <= 30
  8          )
  9  select csum - ccnt
 10  from tsum cross join tcnt;

 CSUM-CCNT
----------
     29022

SQL>