Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    3

    Unanswered: summarize subqueries

    What is the best method to rollup all of my subqueries? I am trying to take the first column in my subqueries and use them at the top level, and the second column of the subqueries would be separate columns at the top level.

    Here is an example:

    select city, cnt, cnt1
    from
    (select FUNC_ID_TO_STR(a.name_id) city, count(a.VI_TC) cnt
    from table1 a, table2 b
    where a.VI_3 (+) = b.VI_3
    and a.VI_6 (+)= b.VI_6
    and a.VI_8 (+) = b.VI_8
    and a.VI_TC (+) = b.VI_TC
    and a.TC_IND = 'Y'
    and A_TMST < SYSDATE - 28
    group by a.name_id)
    ,
    (select FUNC_ID_TO_STR(a.name_id) city, count(a.VI_TC) cnt1
    from table1 a, table2 b
    where a.VI_3 (+) = b.VI_3
    and a.VI_6 (+)= b.VI_6
    and a.VI_8 (+) = b.VI_8
    and a.VI_TC (+) = b.VI_TC
    and a.TC_IND = 'Y'
    and A_TMST < SYSDATE - 21
    and A_TMST > sysdate - 27
    group by a.name_id)
    );

  2. #2
    Join Date
    Apr 2004
    Posts
    3
    Well, figured out the resolution. There isn't one! What I'll have to do is take the result set from each query and then slap it together.

  3. #3
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122
    Quote Originally Posted by cfryett
    What is the best method to rollup all of my subqueries? I am trying to take the first column in my subqueries and use them at the top level, and the second column of the subqueries would be separate columns at the top level.

    Here is an example:

    select city, cnt, cnt1
    from
    (select FUNC_ID_TO_STR(a.name_id) city, count(a.VI_TC) cnt
    from table1 a, table2 b
    where a.VI_3 (+) = b.VI_3
    and a.VI_6 (+)= b.VI_6
    and a.VI_8 (+) = b.VI_8
    and a.VI_TC (+) = b.VI_TC
    and a.TC_IND = 'Y'
    and A_TMST < SYSDATE - 28
    group by a.name_id)
    ,
    (select FUNC_ID_TO_STR(a.name_id) city, count(a.VI_TC) cnt1
    from table1 a, table2 b
    where a.VI_3 (+) = b.VI_3
    and a.VI_6 (+)= b.VI_6
    and a.VI_8 (+) = b.VI_8
    and a.VI_TC (+) = b.VI_TC
    and a.TC_IND = 'Y'
    and A_TMST < SYSDATE - 21
    and A_TMST > sysdate - 27
    group by a.name_id)
    );
    that works at all? I always name my sub queries and then select from them
    select * from
    (selcet * from fred) as John INNER JOIN Bob ON John.FredID = BobID

    I hope that is clear as I hate giveing examples at the best of times.
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, it works (in Oracle at any rate) if you name the subqueries and join them:
    Code:
    SQL> select v1.deptno, v1.sum1, v2.sum2
      2  from
      3  ( select deptno, sum(sal) sum1
      4    from emp
      5    where job = 'MANAGER'
      6    group by deptno
      7  ) v1,
      8  ( select deptno, sum(sal) sum2
      9    from emp
     10    where job = 'CLERK'
     11    group by deptno
     12  ) v2
     13  where v1.deptno = v2.deptno;
    
        DEPTNO       SUM1       SUM2
    ---------- ---------- ----------
            10       2450       1300
            20       2975       1900
            30       2850        950

Posting Permissions

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