I would like to join two tables. A and B. with count.
Table A as the followings:
SQL> select a.book_id, count(a.book_id)
from
a
group by a.book_id ;
BOOK_ID COUNT(A.BOOK_ID)
--------- ----------------
1 2
2 2
3 2
4 2
5 2
6 3
and table B as the followings:
SQL> select b.book_id, count(b.book_id)
from
b
group by b.book_id ;
BOOK_ID COUNT(B.BOOK_ID)
--------- ----------------
6 2
So I would like to have the query which gives me the following result:
BOOK_ID COUNT(A.BOOK_ID) COUNT(B.BOOK_ID)
--------- ---------------- ----------------
1 2 0
2 2 0
3 2 0
4 2 0
5 2 0
6 3 2
I tried this :
SQL> select b.book_id, count(b.book_id),a.book_id, count(a.book_id)
from
b , a
where
b.book_id(+) = a.book_id
group by b.book_id, a.book_id ;
but the results were like this :
BOOK_ID COUNT(B.BOOK_ID) BOOK_ID COUNT(A.BOOK_ID)
--------- ---------------- --------- ----------------
0 1 2
0 2 2
0 3 2
0 4 2
0 5 2
6 6 6 6