Oracle SQL 関数 : SUM : データの合計値を返す
numberRet := SUM( [ALL , DISTINCT] <expr> [ OVER <分析句> ] ) SUMは集計関数、または分析関数として使用します。 <expr>がNULLの項目は計算されません。また、DISTINCT指定の場合は重複行は計算されません。
簡単な例として、単なる合計を求めるSQL文と、GROUP BY句を使用したSQL文を以下に示します。
最初の例は、売上明細の全ての売上金額を合計しています。条件付けのWHERE句がありませんのでTT_売上明細に存在する全ての行が対象になります。
2番目の例は、売上番号毎の売上金額の合計を計算しています。共にSUM関数の中で数量と単価の乗算を行っていますが、括弧の中は<式>が許されますので、<式>として問題の無いものはすべて許されます。
SQL> SELECT SUM(売上数量 * 売上単価) AS 売上金額
2 FROM TT_売上明細;
売上金額
----------
3736000
SQL> SELECT 売上番号, SUM(売上数量 * 売上単価) AS 売上金額
2 FROM TT_売上明細
3 GROUP BY 売上番号
4 ORDER BY 売上番号;
売上番号 売上金額
---------- ----------
1 188000
2 560000
3 570000
4 396000
5 696000
6 454000
8 618000
9 254000
8行が選択されました。
次に2個のテーブルを利用して別の項目でグループ化を行ってSUM関数を利用する例を示します。
最初のSQL文は、TT_売上とTT_売上明細から得意先毎の売上金額の合計を求めます。
WHERE句で売上伝票のヘッダーデータであるTT_売上の売上番号とTT_売上明細の売上番号でリンクを張り、TT_売上の得意先コードでグループ化を行います。（売上先を示す得意先コードはヘッダーにしか持たない為、当然この様になります）
2番目のSQL文は最初とは視点を変えて、担当者ごとの売上金額の合計を求めます。
SQL> SELECT TH.得意先コード, SUM(TD.売上数量 * TD.売上単価) AS 売上金額
2 FROM TT_売上 TH, TT_売上明細 TD
3 WHERE TH.売上番号 = TD.売上番号
4 GROUP BY TH.得意先コード
5 ORDER BY TH.得意先コード;
得意先コード 売上金額
------------ ----------
1 1514000
2 560000
3 396000
4 570000
5 696000
SQL> SELECT TH.担当者コード, SUM(TD.売上数量 * TD.売上単価) AS 売上金額
2 FROM TT_売上 TH, TT_売上明細 TD
3 WHERE TH.売上番号 = TD.売上番号
4 GROUP BY TH.担当者コード
5 ORDER BY TH.担当者コード;
担当者コード 売上金額
------------ ----------
1 1060000
2 396000
4 560000
5 570000
7 696000
9 454000
6行が選択されました。
集計(グループ)の基準となるコードに対応するデータが存在しない場合も表示する方法
上の例では 得意先コード , 担当者コード 毎に集計を表示しましたが、該当するそれぞれの売上データが存在しない場合もあります。 この場合にもデータ行として表示させるには外部結合(LEFT JOIN)を使います。
尚、注意が必要なのは SUM 関数が対象とするのはデータが NULL では無いもののみです。
以下のSQLを見て下さい。 WHERE句の中で TM_得意先 が主体となりますので TT_売上 側の方に (+) を付加します。 さらに TT_売上明細 側にも (+) を付加します。
尚、今回の例の為 TM_得意先 に得意先コードが「6」となるデータを新たに追加しました。
SQL> SELECT TM.得意先コード, SUM(TD.売上数量 * TD.売上単価) AS 売上金額
2 FROM TM_得意先 TM, TT_売上 TH, TT_売上明細 TD
3 WHERE TM.得意先コード = TH.得意先コード(+)
4 AND TH.売上番号 = TD.売上番号(+)
5 GROUP BY TM.得意先コード
6 ORDER BY TM.得意先コード;
得意先コード 売上金額
------------ ----------
1 1514000
2 560000
3 396000
4 570000
5 696000
6
6行が選択されました。
SQL> SELECT TM.担当者コード, SUM(TD.売上数量 * TD.売上単価) AS 売上金額
2 FROM TM_担当者 TM, TT_売上 TH, TT_売上明細 TD
3 WHERE TM.担当者コード = TH.担当者コード(+)
4 AND TH.売上番号 = TD.売上番号(+)
5 GROUP BY TM.担当者コード
6 ORDER BY TM.担当者コード;
担当者コード 売上金額
------------ ----------
1 1060000
2 396000
3
4 560000
5 570000
6
7 696000
8
9 454000
9行が選択されました。
NULLが返される可能性がある SUM には NVL を使用する
上の例では売り上げが存在しない 得意先コード , 担当者コード の行が空白(NULL)の表示でしたが、 SUM の結果に NVL を適用して NULL をゼロに変換すれば表示が正しくなります。 （NULLのままで良い場合にはそのままでOKですが）
SQL> SELECT TM.得意先コード, NVL(SUM(TD.売上数量 * TD.売上単価), 0) AS 売上金額
2 FROM TM_得意先 TM, TT_売上 TH, TT_売上明細 TD
3 WHERE TM.得意先コード = TH.得意先コード(+)
4 AND TH.売上番号 = TD.売上番号(+)
5 GROUP BY TM.得意先コード
6 ORDER BY TM.得意先コード;
得意先コード 売上金額
------------ ----------
1 1514000
2 560000
3 396000
4 570000
5 696000
6 0
6行が選択されました。
SQL> SELECT TM.担当者コード, NVL(SUM(TD.売上数量 * TD.売上単価), 0) AS 売上金額
2 FROM TM_担当者 TM, TT_売上 TH, TT_売上明細 TD
3 WHERE TM.担当者コード = TH.担当者コード(+)
4 AND TH.売上番号 = TD.売上番号(+)
5 GROUP BY TM.担当者コード
6 ORDER BY TM.担当者コード;
担当者コード 売上金額
------------ ----------
1 1060000
2 396000
3 0
4 560000
5 570000
6 0
7 696000
8 0
9 454000
9行が選択されました。
得意先コード毎の SUM を求める部分を副問合せとして FROM 句で処理する方法
上記のSQLを得意先コード毎の SUM を求める部分を1つのSQLとして FROM 句の中で記述し、 その結果と TM_得意先 とリンクする方法があります。
この方法は、副問合せの中をいろんなものにして別の条件でデータを絞り込む場合にも使えると思います。
SQL> SELECT TM.得意先コード, NVL(TX.売上金額, 0) AS 売上金額
2 FROM
3 TM_得意先 TM,
4 ( /* 得意先コード毎の売上金額集計 */
5 SELECT
6 TU.得意先コード
7 ,NVL(SUM(TD.売上数量 * TD.売上単価), 0) AS 売上金額
8 FROM TT_売上 TU , TT_売上明細 TD
9 WHERE TU.売上番号 = TD.売上番号
10 GROUP BY TU.得意先コード
11 ) TX
12 WHERE TM.得意先コード = TX.得意先コード(+)
13 ORDER BY TM.得意先コード;
得意先コード 売上金額
------------ ----------
1 1514000
2 560000
3 396000
4 570000
5 696000
6 0
6行が選択されました。
ちなみに担当者コード毎の方は以下の様になります。
SQL> SELECT TM.担当者コード, NVL(TX.売上金額, 0) AS 売上金額
2 FROM
3 TM_担当者 TM,
4 ( /* 担当者コード毎の売上金額集計 */
5 SELECT
6 TU.担当者コード
7 ,NVL(SUM(TD.売上数量 * TD.売上単価), 0) AS 売上金額
8 FROM TT_売上 TU , TT_売上明細 TD
9 WHERE TU.売上番号 = TD.売上番号
10 GROUP BY TU.担当者コード
11 ) TX
12 WHERE TM.担当者コード = TX.担当者コード(+)
13 ORDER BY TM.担当者コード;
担当者コード 売上金額
------------ ----------
1 1060000
2 396000
3 0
4 560000
5 570000
6 0
7 696000
8 0
9 454000
9行が選択されました。
