Hi all,
I am new to sql. We are using Oracle 10g. I am using the following SQL query that returns the results as follows. But I want to modify the query to return the sum of the counts as temp_id and a_id column values are the same.
Note: A combination of temp_id and a_id can have one or more values in b.id.
Any help is highly appreciated.
Thanks in advance for all your help.
Qry:
Code:
select a.temp_id,
a.id as a_id,
b.id as b_id,
(select count(*) cnt from EMP where id = b.id) as EMP_CNT
from DEPT a, DEPT b
where a.domain = 'X'
and b.domain <> 'X'
and a.temp_id = b.temp_id
AND b.temp_id in (select temp_id from DEPT group by temp_id having count(*) > 2)
Result:
Code:
temp_id A_ID B_ID EMP_CNT
1 11 111 2
1 11 222 5
Expected result:
Code:
temp_id A_ID EMP_CNT
1 11 7 ( 2+5 from abv).