Re: How to get counts and sub_counts of a table with single query?

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1997/06/16
Message-ID: <33a5aa9d.19266512_at_www.sigov.si>#1/1

On Fri, 13 Jun 1997 09:47:45 -0500, Adm Tadj <vahidt_at_dbsun.vitek.com> wrote:

>I'd like to built a VIEW on a SELECT statment which retrieves total
>counts and some other sub_counts which meet certain WHERE conditions.
>A row must be returned even there were zero sub_conts.
>Here is my query and it works as along as all its SELECTs return a
>value otherwise no rows get selected, I am aware of OUTER JOINs but it
>only works if there was only one table/view.
>
>SELECT a.col_a, a.tot_cont, b.sub_cont_b, c.sub_cont_c,.. FROM
> (SELECT col_a, COUNT(*) tot_cont FROM a_table
> GROUP BY col_a) a,
> (SELECT col_a, COUNT(*) sub_cont_b FROM a_table
> WHERE col_x BETWEEN 10 AND 20
> GROUP BY col_a) b,
> (SELECT col_a, COUNT(*) sub_cont_c FROM a_table
> WHERE col_y > 100
> GROUP BY col_a) c
> .
> .
> WHERE a.col_a = b.col_a AND a.col_a = c.col_a;

[Refering to ORACLE RDBMS:]
Your assumption that outer joins are not applicable in your problem is wrong. I think you misunderstood the restriction that a single table in the outer yoin can't be null-generated table for more than one other table, but this is not your case.

For example, if joining three tables A, B and C, you can't use

    "A.a = B.b(+) AND C.c = B.b(+)",
but you can use

    "A.a = B.b(+) AND A.a = C.c(+)"
or

    "A.a = B.b(+) AND B.b = C.c(+)".

So if I apply your problem (I hope I understand it correctly) to SCOTT.EMP table:

SQL> SELECT a.deptno, a.tot_count, b.sub_count_b, c.sub_count_c FROM   2 (SELECT deptno, COUNT(*) tot_count FROM emp   3 GROUP BY deptno) a,
  4 (SELECT deptno, COUNT(*) sub_count_b FROM emp

  5     WHERE sal BETWEEN 2500 AND 4000
  6     GROUP BY deptno) b,
  7    (SELECT deptno, COUNT(*) sub_count_c FROM emp
  8     WHERE sal >= 8000
  9     GROUP BY deptno) c

 10 WHERE a.deptno = b.deptno(+)
 11 AND a.deptno = c.deptno(+)
 12 /

   DEPTNO TOT_COUNT SUB_COUNT_B SUB_COUNT_C --------- --------- ----------- -----------

       10         3
       20         5           3
       30         6           1

3 rows selected.

Regards,

---

Jurij Modic                             Republic of Slovenia
tel: +386 61 178 55 14                  Ministry of Finance
fax: +386 61  21 45 84                  Zupanciceva 3

e-mail: jurij.modic_at_mf.sigov.mail.si Ljubljana 1000

---

Received on Mon Jun 16 1997 - 00:00:00 CEST