SQL subquery COUNT for Oracle-CodePudding

In my Oracle database, I have two tables T1 with primary key k1, and T2 with a composite primary key k1, k2. I would like to select all columns in T1 along with the number of lines in T2 such as T1.k1 = T2.k1.

It seems simple, but I can't figure how to use the COUNT function to get this result, any idea ?

CodePudding user response：

I don't have your tables so I'll try to illustrate it using Scott's sample emp and dept tables:

SQL> select * from dept t1 order by t1.deptno;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> select deptno, empno, ename from emp order by deptno;

    DEPTNO      EMPNO ENAME
---------- ---------- ----------
        10       7782 CLARK     --> 3 employees in deptno 10
        10       7839 KING
        10       7934 MILLER
        20       7566 JONES     --> 5 employees in deptno 20
        20       7902 FORD
        20       7876 ADAMS
        20       7369 SMITH
        20       7788 SCOTT
        30       7521 WARD      --> 6 employees in deptno 30
        30       7844 TURNER
        30       7499 ALLEN
        30       7900 JAMES
        30       7698 BLAKE
        30       7654 MARTIN
                                --> 0 employees in deptno 40
14 rows selected.

SQL>

A few options you might try:

Correlated subquery:

SQL> select t1.*,
  2         (select count(*) from emp t2 where t2.deptno = t1.deptno) cnt
  3  from dept t1
  4  order by t1.deptno;

    DEPTNO DNAME          LOC                  CNT
---------- -------------- ------------- ----------
        10 ACCOUNTING     NEW YORK               3
        20 RESEARCH       DALLAS                 5
        30 SALES          CHICAGO                6
        40 OPERATIONS     BOSTON                 0

SQL>

(Outer) join with the COUNT function and the GROUP BY clause:

SQL> select t1.*, count(t2.rowid) cnt
  2  from dept t1 left join emp t2 on t2.deptno = t1.deptno
  3  group by t1.deptno, t1.dname, t1.loc
  4  order by t1.deptno;

    DEPTNO DNAME          LOC                  CNT
---------- -------------- ------------- ----------
        10 ACCOUNTING     NEW YORK               3
        20 RESEARCH       DALLAS                 5
        30 SALES          CHICAGO                6
        40 OPERATIONS     BOSTON                 0

SQL>

(Outer) join with the COUNT function in its analytic form:

SQL> select distinct t1.*,
  2                  count(t2.rowid) over (partition by t1.deptno) cnt
  3  from dept t1 left join emp t2 on t2.deptno = t1.deptno
  4  order by t1.deptno;

    DEPTNO DNAME          LOC                  CNT
---------- -------------- ------------- ----------
        10 ACCOUNTING     NEW YORK               3
        20 RESEARCH       DALLAS                 5
        30 SALES          CHICAGO                6
        40 OPERATIONS     BOSTON                 0

SQL>