できるPRO MySQL できるPROシリーズ Kindle版の内容をまとめる。
GROUP BY
句WITH ROLLUP
を指定すると,統計(全データの合計)が表示される。GROUP BY 列名 [,…] [WITH ROLLUP]
SELECT Continent, count(*) FROM country
GROUP BY Continent;
MariaDB [world]> SELECT Continent, count(*) FROM country GROUP BY Continent;
+---------------+----------+
| Continent | count(*) |
+---------------+----------+
| Asia | 51 |
| Europe | 46 |
| North America | 37 |
| Africa | 58 |
| Oceania | 28 |
| Antarctica | 5 |
| South America | 14 |
+---------------+----------+
7 rows in set (0.00 sec)
SELECT continent, SUM(Population) FROM country
GROUP BY continent WITH ROLLUP;
MariaDB [world]> SELECT continent, SUM(Population) FROM country GROUP BY continent WITH ROLLUP;
+---------------+-----------------+
| continent | SUM(Population) |
+---------------+-----------------+
| Asia | 3705025700 |
| Europe | 730074600 |
| North America | 482993000 |
| Africa | 784475000 |
| Oceania | 30401150 |
| Antarctica | 0 |
| South America | 345780000 |
| NULL | 6078749450 |
+---------------+-----------------+
8 rows in set (0.01 sec)