過去の「SQLで小計と合計を取得する (SQL: Subtotal and Grand Total)」では、オラクルDBでの集計を行いました。
たまたまMySQLを使う機会があったため、今回はMySQLを使用して小計と合計を求めます。構文と結果は以下の通りです。
List 1: createData.pl
mysql> select deptno, job, sum(sal) as sum_sal
-> from EMP
-> group by deptno, job with rollup;
+--------+-----------+---------+
| deptno | job | sum_sal |
+--------+-----------+---------+
| 10 | CLERK | 1300 |
| 10 | MANAGER | 2450 |
| 10 | PRESIDENT | 5000 |
| 10 | NULL | 8750 |
| 20 | ANALYST | 6000 |
| 20 | CLERK | 1100 |
| 20 | MANAGER | 2975 |
| 20 | NULL | 10075 |
| 30 | CLERK | 950 |
| 30 | MANAGER | 2850 |
| 30 | SALESMAN | 5600 |
| 30 | NULL | 9400 |
| NULL | NULL | 28225 |
+--------+-----------+---------+
13 rows in set (0.00 sec)
Dev Shed Forumsではcoalesceを使用したサンプルが紹介されています。
http://forums.devshed.com/mysql-help-4/group-by-with-rollup-replace-null-value-580155.html
List 2: Workaround 1
select coalesce( mth , 'GRAND TOTAL' ) as dates,
sum( salary ) as sum_salary
from ( select month(date) as mth,
salary
from tb1 ) as dt
group by mth with rollup
stackoverflow では、ifnullを使用したサンプルが紹介されています。この例では複数列の集計を一度に行っています。
http://stackoverflow.com/questions/6822666/how-can-i-replace-null-category-titles-in-mysql-rollup-function
List 3: Workaround 2
SELECT IFNULL(name, 'MainTotal') AS name,
IFNULL(location, 'SubTotal') AS location,
SUM(IF(division='OEM',totalHours,NULL)) AS OEM,
SUM(IF(division='A/M',totalHours,NULL)) AS AM,
SUM(IF(division='SKF',totalHours,NULL)) AS SKF,
SUM(IF(division='RE',totalHours,NULL)) AS RE,
location as location
from $databasetable
group by location, name with rollup
なお、明記はされていませんが、これらの回避策は集計キーとなる列にNull値が含まれていない事を前提としていますので注意が必要です。上記のサンプルを使用する際には、「元データにNULLが含まれていない」という前提を確認して下さい。
なお、MySQLには、集計列を判別する為の関数(Oracleのgrouping関数に相当)が提供されていないようです。
参考資料:マニュアル(日本語: MySQL 5.1)
http://dev.mysql.com/doc/refman/5.1/ja/group-by-modifiers.html
[Summary]
Not only Oracle, but MySQL also provides rollup function. List 1, List 2 and List 3 shows the basic usage.
You may need to determine the summarized row, like Oracle "grouping" function. But MySQL does not provide that kind of function.
References:
Manual (English: MySQL 5.6)
http://dev.mysql.com/doc/refman/5.6/en/group-by-modifiers.html
Workaround (Dev Shed Forums)
http://forums.devshed.com/mysql-help-4/group-by-with-rollup-replace-null-value-580155.html
Workaround (stackoverflow)
http://stackoverflow.com/questions/6822666/how-can-i-replace-null-category-titles-in-mysql-rollup-function