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行が選択されました。