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 ?
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>
