Oracleデータベースで小計や総合計、平均値、最小値、最大値などを算出する方法を説明します。小計業や総合計行を表示しようとすると、複雑なSQLを記述しなければならなかったりしますが、ここで紹介するテクニックを使うことで、とてもシンプルなSQLで小計や総合計、平均値、最小値、最大値を自動的に表示することが可能です。それでは早速その方法をそれぞれ見ていきましょう。
テスト用のテーブルとデータを用意する
まずこの記事で利用するテーブルとデータを用意します。以下のように「employee」という従業員テーブルを作成し、emp_id(従業員ID)、emp_name(従業員名)、dept_no(部署番号)、age(年齢)という列を定義します。このemployeeテーブルに6件のテストデータを追加します。
CREATE TABLE employee (
emp_id NUMBER,
emp_name VARCHAR2(50),
dept_no NUMBER,
age NUMBER
);
INSERT INTO employee VALUES (1, 'aaa', 10, 22);
INSERT INTO employee VALUES (2, 'bbb', 20, 43);
INSERT INTO employee VALUES (3, 'ccc', 10, 31);
INSERT INTO employee VALUES (4, 'ddd', 10, 25);
INSERT INTO employee VALUES (5, 'eee', 20, 55);
INSERT INTO employee VALUES (6, 'fff', 30, 38);これで次のようなデータを準備できました。このデータを用いて小計や総合計を自動出力できるようにしてみましょう。
SQL> SELECT * FROM employee ORDER BY emp_id;
EMP_ID EMP_NAME DEPT_NO AGE
_________ ___________ __________ ______
1 aaa 10 22
2 bbb 20 43
3 ccc 10 31
4 ddd 10 25
5 eee 20 55
6 fff 30 38
6 rows selected.小計を自動的に表示する方法
SQL*PlusやSQLclには、次のように小計を自動表示する機能が備わっています。「BREAK ON [列名]」というコマンドで、指定した列名の列値に応じて中断(ブレーク)を指示します。つまり指定した列値が変わる度に処理を実行するというコマンドで、これにより列値が変わるタイミングで集計処理を実行できるようになります。。そして「COMPUTE SUM OF [小計を出したい列名] ON [中断する列名]」を指定します。この２つをあらかじめ指示しておくことで、同じグループの値は小計が計算される対象となります。
BREAK ON dept_no
COMPUTE SUM OF age ON dept_noここではdept_no(部署番号)毎に、emp_name(従業員名)とage(年齢)の小計を出すようにしています。
SQL> BREAK ON dept_no
SQL> COMPUTE SUM OF age ON dept_no
SQL> SELECT dept_no, emp_name, age FROM employee ORDER BY dept_no;
DEPT_NO EMP_NAME AGE
---------- -------------------------------------------------- ----------
10 aaa 22
ccc 31
ddd 25
********** ----------
sum 78
20 eee 55
bbb 43
********** ----------
sum 98
30 fff 38
********** ----------
sum 38
6 rows selected.このようにすることで、同じdept_no(部署番号)を持つレコードが小計計算の対象となり、「sum」という小計行が追加されて合計値が表示されるようになります。このようにして同じグループの値の小計を簡単に出力できるようになります。
しかしこれでは小計行がどれなのかパット見ただけではわかりにくいですので、小計行にラベルを付けてあげましょう。以下のように「LABEL SUBTOTAL」と設定して、小計行の名前を「SUBTOTAL」にしてみましょう。
BREAK ON dept_no
COMPUTE SUM LABEL SUBTOTAL OF age ON dept_noすると次のように小計行に「SUBTOTAL」という名前がついてわかりやすくなります。
SQL> BREAK ON dept_no
SQL> COMPUTE SUM LABEL SUBTOTAL OF age ON dept_no
SQL> SELECT dept_no, emp_name, age FROM employee ORDER BY dept_no;
DEPT_NO EMP_NAME AGE
---------- -------------------------------------------------- ----------
10 aaa 22
ccc 31
ddd 25
********** ----------
SUBTOTAL 78
20 eee 55
bbb 43
********** ----------
SUBTOTAL 98
30 fff 38
********** ----------
SUBTOTAL 38
6行が選択されました。なお、一度小計を計算するBREAKコマンドやCOMPUTEコマンドを実行すると、移行は自動的に小計を計算するようになってしまいます。この状態でそのままSQLを実行すると次のようにエラーになってしまいます。
SQL> SELECT * FROM employee;
EMP_ID EMP_NAME DEPT_NO AGE
---------- -------------------------------------------------- ---------- ----------
1 aaa 10 22
2 bbb 20 43
3 ccc 10 31
********** ----------
sum 78
次のコマンドの開始中にエラーが発生しました : 行 1 -
SELECT * FROM employee
エラー・レポート -
SP2-: エントリが選択されていません。BREAK/COMPUTEコード - ORDER BYが指定されていない可能性があります。
More Details :そのような場合には、次の2つのコマンドを実行して小計の集計設定をクリアして上げる必要があります。
CLEAR BREAKS
CLEAR COMPUTESこの2つのコマンドを実行してあげることで小計計算設定がクリアされて、再度他のSQLを実行できるようになります。
SQL> CLEAR BREAKS
クリアされたブレーク
SQL> CLEAR COMPUTES
クリアされた計算
SQL> SELECT dept_no, emp_name, age FROM employee ORDER BY dept_no;
DEPT_NO EMP_NAME AGE
__________ ___________ ______
10 aaa 22
10 ccc 31
10 ddd 25
20 eee 55
20 bbb 43
30 fff 38
6行が選択されました。総合計を自動的に表示する方法
ある列の総合計を自動的に算出して表示できるようにするためには、SQL実行前に次の「BREAK ON REPORT」コマンドと、「COMPUTE SUM LABEL TOTAL OF [列名] ON REPORT」を実行しておきます。その後、SQLを実行します。「BREAK ON REPORT」というのは、レポートの最後に指定された処理を実行するという指定になります。先の小計算出では「BREAK ON [列名]」として、列値が変わる度に集計処理が実行されていましたが、「BREAK ON REPORT」にすることによって、データが全て出力された最後に指定した処理を実行できるというコマンドになります。
BREAK ON REPORT
COMPUTE SUM LABEL TOTAL OF age ON REPORT
SELECT dept_no, emp_name, age FROM employee ORDER BY dept_no;そしてSQLを実行することによって、列名で指定したage列の総合系が自動的に計算されてTOTAL列として表示されます。
SQL> SELECT dept_no, emp_name, age FROM employee ORDER BY dept_no;
DEPT_NO EMP_NAME AGE
---------- -------------------------------------------------- ----------
10 aaa 22
10 ccc 31
10 ddd 25
20 eee 55
20 bbb 43
30 fff 38
----------
TOTAL 214
6行が選択されました。「LABEL TOTAL」という部分は、総合計を表示する行のラベル名となっていますので、別の名前を指定しても問題ありません。次のように総合計のラベルを「合計」と設定しても問題なく表示されます。
SQL> BREAK ON REPORT
SQL> COMPUTE SUM LABEL "合計" OF age ON REPORT
SQL> SELECT dept_no, emp_name, age FROM employee ORDER BY dept_no;
DEPT_NO EMP_NAME AGE
---------- -------------------------------------------------- ----------
10 aaa 22
10 ccc 31
10 ddd 25
20 eee 55
20 bbb 43
30 fff 38
----------
合計 214
6行が選択されました。「LABEL」で合計業のラベルを指定せずに実行すると、次のようにラベル名が「sum」となってしまいますので、先に指定したように「LABEL TOTAL」のようにした方がわかりやすいでしょう。
SQL> BREAK ON REPORT
SQL> COMPUTE SUM OF age ON REPORT
SQL> SELECT dept_no, emp_name, age FROM employee ORDER BY dept_no;
DEPT_NO EMP_NAME AGE
---------- -------------------------------------------------- ----------
10 aaa 22
10 ccc 31
10 ddd 25
20 eee 55
20 bbb 43
30 fff 38
----------
sum 214
6行が選択されました。複数列の総合計を出力する方法
ここまでの例では、単一行の総合計だけを出力していましたが、複数行の総合計を出力することもできます。例えば次のようなデータの年齢(age)と従業員ID(emp_id)の総合計を出力してみます。
SQL> SELECT dept_no, emp_name, age, emp_id FROM employee ORDER BY dept_no;
DEPT_NO EMP_NAME AGE EMP_ID
__________ ___________ ______ _________
10 aaa 22 1
10 ccc 31 3
10 ddd 25 4
20 eee 55 5
20 bbb 43 2
30 fff 38 6
6 rows selected.このデータの年齢(age)と従業員ID(emp_id)の両方の総合計を自動的に出力するには次のようにします。
BREAK ON REPORT
COMPUTE SUM LABEL TOTAL OF age emp_id ON REPORTこのようにage列とemp_id列を集計対象として指定することによって、2つの列の総合計を自動的に出力することが出来ます。
SQL> BREAK ON REPORT
SQL> COMPUTE SUM LABEL TOTAL OF age emp_id ON REPORT
SQL> SELECT dept_no, emp_name, age, emp_id FROM employee ORDER BY dept_no;
DEPT_NO EMP_NAME AGE EMP_ID
---------- -------------------------------------------------- ---------- ----------
10 aaa 22 1
10 ccc 31 3
10 ddd 25 4
20 eee 55 5
20 bbb 43 2
30 fff 38 6
---------- ----------
TOTAL 214 21
6 rows selected.総合計・平均値・最小値・最大値なども自動生成する
ここまでは小計と総合計を自動的に出力できるようにしてきましたが、それ以外にも平均値、最小値、最大値を自動計算して出力させることもできます。以下のように各関数をCOMPUTEコマンドに並べていきます。
| SUM | 合計値 |
| AVG | 平均値 |
| MINIMUM | 最小値 |
| MAXIMUM | 最大値 |
| COUNT | NULLではない行数 |
| NUMBER | 行数 |
| STD | 標準偏差値 |
| VARIANCE | 分散値 |
ここでは、SUM、AVG、MINIMUM、MAXIMUM、COUNT、NUMBERを一度に設定します。
BREAK ON REPORT
COMPUTE SUM LABEL TOTAL AVG LABEL AVERAGE MINIMUM LABEL MINIMUM MAXIMUM LABEL MAXIMUM COUNT LABEL COUNT NUMBER LABEL NUMBER OF age emp_id ON REPORTこの状態でSQLを実行すると、次のように合計値、平均値、NULLでない行数、最小値、最大値、行数が最後に出力されていることが分かります。
SQL> BREAK ON REPORT
SQL> COMPUTE SUM LABEL TOTAL AVG LABEL AVERAGE MINIMUM LABEL MINIMUM MAXIMUM LABEL MAXIMUM COUNT LABEL COUNT NUMBER LABEL NUMBER OF age emp_id ON REPORT
SQL> SELECT dept_no, emp_name, age, emp_id FROM employee ORDER BY dept_no;
DEPT_NO EMP_NAME AGE EMP_ID
---------- -------------------------------------------------- ---------- ----------
10 aaa 22 1
10 ccc 31 3
10 ddd 25 4
20 eee 55 5
20 bbb 43 2
30 fff 38 6
---------- ----------
AVERAGE 35.6666667 3.5
TOTAL 214 21
COUNT 6 6
MINIMUM 22 1
MAXIMUM 55 6
NUMBER 6 6
6 rows selected.まとめ
これまで見てきたように、SQLでいちいち小計や総合計値や平均値を計算したりする必要なく、とても簡単にシンプルなSQLで自動計算された小計や総合計値や平均値などを自動出力させることが出来ます。この機能を使ってより良いレポートを作成することが出来るようになりますので、ぜひ利用してみて下さい。
