5-1 nvl関数の引数に集合関数

SQLパズル

マスタテーブル
工場コード  工場名
----------  ------
a1          元町
a2          田原
a3          高岡
a4          三好

トランザクションテーブル
年月日      工場コード   生産高
----------  ----------  ------
2001/08/01  a1             100
2001/08/01  a3             100
2001/08/02  a1             150
2001/08/02  a3             200

トランザクションテーブルにおける、工場別の生産高総合計を出力する。

マスタテーブルに登録されているが、
トランザクションテーブルに存在しない工場の生産高は0とする。

出力結果
工場コード  工場名   生産高総合計
----------  ------  ------------
a1          元町             250
a2          田原               0
a3          高岡             300
a4          三好               0

データ作成スクリプト

create table マスタテーブル(
工場コード char(2),
工場名 varchar2(4));

create table トランザクションテーブル(
年月日 date,
工場コード char(2),
生産高 number);

insert into マスタテーブル(工場コード,工場名) values('a1','元町');
insert into マスタテーブル(工場コード,工場名) values('a2','田原');
insert into マスタテーブル(工場コード,工場名) values('a3','高岡');
insert into マスタテーブル(工場コード,工場名) values('a4','三好');
insert into トランザクションテーブル(年月日,工場コード,生産高) values('2001/08/01','a1',100);
insert into トランザクションテーブル(年月日,工場コード,生産高) values('2001/08/01','a3',100);
insert into トランザクションテーブル(年月日,工場コード,生産高) values('2001/08/02','a1',150);
insert into トランザクションテーブル(年月日,工場コード,生産高) values('2001/08/02','a3',200);
commit;

SQL

select 工場コード,工場名,
(select nvl(sum(b.生産高),0) from トランザクションテーブル b
 where b.工場コード=a.工場コード) as 生産高総合計
from マスタテーブル a
order by 工場コード;

解説

工場コードと生産高総合計は、1対1となるので
select句でスカラー問い合わせを使って、生産高総合計を取得できます。

sum関数は、集計元となる集合が空集合の場合にnullを返しますので、
nvl関数で、nullの場合は0に変換してます。