select a.a1,count(b.a1),count(c.a1) from a,b,c
where a.a1 = b.a1(+) and a.a1 = c.a1(+)
group by a1
でcount(b.a1)とcount(c.a1)の値がどのようにでてくるかの理解が違っています。
★a.a1
1
2
3
★b.a1,a2
1,101
1,102
2,201
★c.a1,a3
2,222
3,333
3,444
というデータになっていると、
select a.a1,b.a1,b.a2,c.a1,c.a3 from a,b,c
where a.a1 = b.a1(+) and a.a1 = c.a1(+)
は、
a.a1,b.a1,b.a2,c.a1,c.a3
1,1,101,,
1,1,102,,
2,2,201,2,222
3,,3,333
3,,3,444
となります。
で、
select a.a1,count(b.a1),count(c.a1) from a,b,c
where a.a1 = b.a1(+) and a.a1 = c.a1(+)
group by a1
は、
1,2,2
2,1,1
3,2,2
となります。
b.a1やc.a1がNullであろうが値が入っていようが、1件としてカウントされます。
select a.a1,sum(case b.a1 isnull then 1 else 0 end)
,sum(case c.a1 isnull then 1 else 0 end) from a,b,c
where a.a1 = b.a1(+) and a.a1 = c.a1(+)
group by a1
なんてしたら、
1,2,0
2,1,1
3,0,2
とでてきます。
方法１：がんばってこれでやってみる。
select shohin.shohin_no as "商品NO"
,sum(case when yoyaku_1.shohin_no is null then 0 else 1 end) as "予約数"
,sum(case when receipt_1.shohin_no is null then 0 else 1 end) as "予約明細数"
,sum(case when yoyaku_2.shohin_no is null then 0 else 1 end) as "受取数"
,sum(case when receipt_2.shohin_no is null then 0 else 1 end) as "受取明細数"
from shohin
,(select * from yoyaku where yoyaku.status = '1') yoyaku_1
,(select * from yoyaku where yoyaku.status = '2') yoyaku_2
,(select * from receipt where receipt.status = '1') receipt_1
,(select * from receipt where receipt.status = '2') receipt_2
where yoyaku_1.shohin_no(+) = shohin.shohin_no
and yoyaku_2.shohin_no(+) = shohin.shohin_no
and receipt_1.shohin_no(+) = yoyaku_1.shohin_no
and receipt_1.status(+) = yoyaku_1.status
and receipt_2.shohin_no(+) = yoyaku_2.shohin_no
and receipt_2.status(+) = yoyaku_2.status
group by shohin.shohin_no
order by 1
どう考えても処理遅そう。
方法２：group by した結果を結合する。
select shohin.shohin_no as "商品NO"
,nvl(yoyaku_1.cnt,0) as "予約数"
,nvl(receipt_1.cnt,0) as "予約明細数"
,nvl(yoyaku_2.cnt,0) as "受取数"
,nvl(receipt_2.cnt,0) as "受取明細数"
from shohin
,(select shohin_no,count(*) as cnt from yoyaku
where yoyaku.status = '1' group by shohin_no) yoyaku_1
,(select shohin_no,count(*) as cnt from yoyaku
where yoyaku.status = '2' group by shohin_no) yoyaku_2
,(select shohin_no,count(*) as cnt from receipt
where receipt.status = '1' group by shohin_no) receipt_1
,(select shohin_no,count(*) as cnt from receipt
where receipt.status = '2' group by shohin_no) receipt_2
where yoyaku_1.shohin_no(+) = shohin.shohin_no
and yoyaku_2.shohin_no(+) = shohin.shohin_no
and receipt_1.shohin_no(+) = shohin.shohin_no
and receipt_2.shohin_no(+) = shohin.shohin_no
group by shohin.shohin_no
order by 1
注：nvl(yoyaku_1.cnt,0)は、yoyaku_1.cntがNull(対象商品がない)時に0を表示します。
方法３：スカラー副問い合わせを使う。
select shohin.shohin_no as "商品NO",
(select count(*)
from yoyaku where yoyaku.status = '1'
and yoyaku.shohin_no = shohin.shohin_no
) as "予約数",
(select count(*)
from receipt where receipt.status = '1'
and receipt.shohin_no = shohin.shohin_no
) as "予約明細数",
(select count(*)
from yoyaku where yoyaku.status = '2'
and yoyaku.shohin_no = shohin.shohin_no
) as "受取数",
(select count(*)
from receipt where receipt.status = '1'
and receipt.shohin_no = shohin.shohin_no
) as "受取明細数"
from shohin
order by 1
※receiptはshohinと結合すべきでないのかもしれませんが、
あってはいけないyoyakuと結合できないreceiptは
（質問のようにyoyakuとreceiptを結合するとカウントされないが、
この回答ではカウントされてしまう。）
質問に示された【想定結果】では調べようがないので、
別途調べるselect文を実行するだろうから、手っ取り早く、
shohinと結合してしまいました。
## 大量データを投入して、全部動かしてみてどれが早いか試してみるのも、
## 勉強のうちなのかも。
お礼
こんな時間に素早いご回答、感謝します！ 解説もとてもわかりやすかったです。 まず1回グループ化する前の全体像を把握すべきだったのですね。 また、改善案を3つも書いて頂き、本当にありがとうございます。 試してみたところ、しっかり想定通りの結果が出ました。 (方法1はうまくいきませんでしたが…これは自分で確かめてみます！) 大量データは、ちょっと今日はもう遅いので、明日明後日辺りに試してみたいと思います。 これで今日はぐっすり眠れそうです(笑) 本当にありがとうございました。