group byにより集計した結果での名称取得方法

質問

次のようなテーブルがあったとして、一回のSQLで商品名称とその売上件数の一覧を作成することは可能なのでしょうか？

【売上テーブル】
ID 販売日 商品ID
0001 2011/01/01 1
0002 2011/01/01 2
0003 2011/01/03 1
0004 2011/01/10 1
0005 2011/01/12 2
・・・

【商品マスタ】
ID 商品名
1 りんご
2 みかん
3 いちご

【最終的に作成したい一覧】
商品名 件数
りんご 3
みかん 2
いちご 0

※売上テーブルに存在しない商品についても、件数が0件として一覧に出力出来るようにしたいです。

select 商品ID from 売上テーブル group by 商品ID
にて商品ID毎の件数を算出し、それとは別に
select ID,商品名 from 商品マスタ
として、商品IDと名称のリストを取得後、アプリにてこれらの情報を突き合わせれば実現できるのは分かるのですが、これらの処理を一回のSQLにまとめることは可能なのでしょうか？

よろしくお願いします。

回答No.1

noname#210417 · ベストアンサー

商品マスタをメインにして売上テーブルを左外部結合すれば、1回のSQLでデータ取得できるはずです。

select 商品マスタ.商品名,Count(商品マスタ.商品名)
from 商品マスタ left outer join 売上テーブル
where 売上テーブル.商品ID = 商品マスタ.商品ID
group by 商品マスタ.商品名

こんな感じのSQL分でお望みの結果が出ると思うのですが・・・SQL Serverの環境がないので検証出来ません。

参考URL：

http://uchukamen.com/SQL2005/Select/Select.htm

お礼

ご回答頂きどうもありがとうございます。
ご教授頂いたSQLだと私もうまくいくような気がしていたのですが、実際に試してみると、なぜか0件のものについては表示されません。（LEFT JOINをRIGHT JOIN、INNER JOINに変更してみても駄目でした）
group byの場合は左外部結合がうまくきかないことがあるのでしょうか？

補足

ご回答頂きどうもありがとうございました。
あれから試行錯誤した結果、次のようなSQLで実現できました。
SELECT 商品ID
,商品名
,isnull(tbl.件数, 0) 件数
from
(select 商品ID, COUNT(*) 件数
from 売上テーブル
group by 商品ID
) as tbl
right join 商品マスタ
on 商品マスタ.商品ID = tbl.商品ID

回答No.3

nora1962

> 【商品マスタ】
> ID 商品名
とあるのに、試されているSQLでは「商品マスタ.商品ID」になっているのが気になります。
テーブルのカラム名について確認されてはいかがですか。

お礼

ご回答頂きどうもありがとうございます。
あれから試行錯誤した結果、次のようなSQLで実現できました。
SELECT 商品ID
,商品名
,isnull(tbl.件数, 0) 件数
from
(select 商品ID, COUNT(*) 件数
from 売上テーブル
group by 商品ID
) as tbl
right join 商品マスタ
on 商品マスタ.商品ID = tbl.商品ID

回答No.2

marutone

以下のソースをご参考ください。

SELECT
　　商品マスタ.商品名,
　　COUNT(*)
FROM
　　商品マスタ LEFT OUTER JOIN
　　売上テーブル ON 商品マスタ.商品ID = 売上テーブル.商品ID
GROUP BY
　　商品マスタ.商品ID,
　　商品マスタ.商品名

お礼

ご回答頂きどうもありがとうございます。
ご教授頂いたSQLだと私もうまくいくような気がしていたのですが、実際に試してみると、なぜか0件のものについては表示されません。（LEFT JOINをRIGHT JOIN、INNER JOINに変更してみても駄目でした）
group byの場合は左外部結合がうまくきかないことがあるのでしょうか？

補足

ご回答頂きどうもありがとうございました。
あれから試行錯誤した結果、次のようなSQLで実現できました。
SELECT 商品ID
,商品名
,isnull(tbl.件数, 0) 件数
from
(select 商品ID, COUNT(*) 件数
from 売上テーブル
group by 商品ID
) as tbl
right join 商品マスタ
on 商品マスタ.商品ID = tbl.商品ID