dBforums - View Single Post - sum of record counts provided by correlated subquery

Peter.Vanroose · #3 (**permalink**) 06-28-09, 08:29

Or use a common table expression (CTE) instead of the nested table expression:

Code:

WITH derivedtable(temp_id, a_id, emp_cnt) AS
(select a.temp_id, a.id,
        (select count(*) from EMP where id = b.id)
 from   DEPT a inner join DEPT b on a.temp_id = b.temp_id
 where  a.domain = 'X'
   and  b.domain <> 'X'
   AND  b.temp_id in
        (select temp_id from DEPT group by temp_id having count(*) > 2)
)
SELECT temp_id, a_id, SUM(emp_cnt) AS emp_cnt
  FROM derivedtable
GROUP BY temp_id, a_id