MySQLでの小計と合計 (MySQL: Subtotal and Grand Total)

過去の「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