summarize subqueries

cfryett · #1 (**permalink**) 08-13-04, 15:39

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

cfryett · #2 (**permalink**) 08-13-04, 16:08

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.

Matt_T_hat · #3 (**permalink**) 08-16-04, 12:00

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.

andrewst · #4 (**permalink**) 08-16-04, 12:20

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

Internet Services

Web Development

Digital Marketing

Consumer Tech