sum of record counts provided by correlated subquery

joe robles · #1 (**permalink**) 06-27-09, 19:29

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).

r937 · #2 (**permalink**) 06-27-09, 21:52

Code:

SELECT temp_id
     , a_id
     , SUM(emp_cnt) AS emp_cnt
  FROM (
       /* insert your query here */ 
       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)
       ) AS derivedtable
GROUP
    BY temp_id
     , a_id

Peter.Vanroose · #3 (**permalink**) 06-28-09, 07: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

joe robles · #4 (**permalink**) 06-30-09, 22:12

Thank you all for the valuable input.

joe robles · #5 (**permalink**) 07-03-09, 16:31

Quote:

Originally Posted by r937

Code:

SELECT temp_id
     , a_id
     , SUM(emp_cnt) AS emp_cnt
  FROM (
       /* insert your query here */ 
       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)
       ) AS derivedtable
GROUP
    BY temp_id
     , a_id

One more question. I have a further requirement - given a user specified number say 4000, a program needs to pick randomly 4000 temp_ids from the temp_ids existing in the database.

I could hold in memory all the temp_ids that are the result of the SQL (Please refer to the SQL suggested by you, from above).

But I am not sure if I want to a hold large number of temp_ids in memory and then use random to pick temp_ids.

So is there a way in Oracle 10g that allows usage of random in SQL statement itself?..so that the SQL picks 4000 DIFFERENT temp_ids from the database?

If so, can it be used in the type of SQL from above?

Thanks in advance for your input.