Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2009
    Posts
    10

    Unanswered: sum of record counts provided by correlated subquery

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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Jun 2009
    Posts
    10
    Thank you all for the valuable input.

  5. #5
    Join Date
    Jun 2009
    Posts
    10

    Random usage in correlated subquery

    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by joe robles
    So is there a way in Oracle 10g ...
    sorry, mate, i have no idea

    this is the ANSI SQL forum

    perhaps you could ask one of the site's moderators to move this thread to the oracle forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •