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行が選択されました。 |
本書用のWebアプリを使ってSQLを実行し学習していきます。初心者の方に分かりやすく、図表を多く使って解説されています。 学習の最後には200問越えのドリルが付いていてSQLのスキルが試せる様になっています。