Group byやWindows関数でも、勿論集計出来ますが。ROLLUPを利用する事でグループ毎に集計した値を取得すると同時に集計した結果も合わせて取得する事が出来ます。データ集計を簡素化する上で、Windows関数やGroup byを使い慣れておくと非常に便利です。MySQLでも8.0からWindows関数が利用出来る様になっています。Group by + Rollupは古いMySQL5.6等でも利用する事が可能です。
PostgreSQL
- Windows Functionで集計
POC=# WITH Sales AS (
SELECT 123 AS sku, 1 AS day, 9.99 AS price UNION ALL
SELECT 123, 1, 8.99 UNION ALL
SELECT 456, 1, 4.56 UNION ALL
SELECT 123, 2, 9.99 UNION ALL
SELECT 789, 3, 1.00 UNION ALL
SELECT 456, 3, 4.25 UNION ALL
SELECT 789, 3, 0.99
)
SELECT
sku,
day,
SUM(price) over (partition by sku,day) as "total" from Sales;
sku | day | total
-----+-----+-------
123 | 1 | 18.98
123 | 1 | 18.98
123 | 2 | 9.99
456 | 1 | 4.56
456 | 3 | 4.25
789 | 3 | 1.99
789 | 3 | 1.99
(7 rows)
Time: 1.744 ms
- Group by +Rollupで小計を追加
POC=# WITH Sales AS (
SELECT 123 AS sku, 1 AS day, 9.99 AS price UNION ALL
SELECT 123, 1, 8.99 UNION ALL
SELECT 456, 1, 4.56 UNION ALL
SELECT 123, 2, 9.99 UNION ALL
SELECT 789, 3, 1.00 UNION ALL
SELECT 456, 3, 4.25 UNION ALL
SELECT 789, 3, 0.99
)
SELECT
sku,
day,
SUM(price) AS total
FROM Sales
GROUP BY ROLLUP(sku, day)
ORDER BY sku, day;
sku | day | total
-----+-----+-------
123 | 1 | 18.98
123 | 2 | 9.99
123 | | 28.97
456 | 1 | 4.56
456 | 3 | 4.25
456 | | 8.81
789 | 3 | 1.99
789 | | 1.99
| | 39.77
(9 rows)
Time: 1.350 ms
POC=#
上記の例ではテーブル自体をバッファー上に作成しているので以下の様な結果になっています。

参照: 9.22. ウィンドウ関数 / 7.2. テーブル式
備考:PostgreSQLにおけるWindows関数の処理
POC=# select * from members limit 10;
id | name | age | salary | description | regist
----+------+-----+--------+-------------+------------
1 | Mr.T | 20 | 100 | | 2022-03-18
2 | Mr.U | 21 | 101 | | 2022-03-18
3 | Mr.V | 22 | 102 | | 2022-03-18
5 | Mr.X | 22 | 100 | | 2022-04-01
(4 rows)
Time: 1.052 ms
POC=# select id,name,age,salary,Row_Number() over(partition by age order by salary desc) as "row_number",
rank() over(partition by age order by salary desc) as "rank",
dense_rank() over(partition by age order by salary desc) as "dense_rank"
from members;
id | name | age | salary | row_number | rank | dense_rank
----+------+-----+--------+------------+------+------------
1 | Mr.T | 20 | 100 | 1 | 1 | 1
2 | Mr.U | 21 | 101 | 1 | 1 | 1
3 | Mr.V | 22 | 102 | 1 | 1 | 1
5 | Mr.X | 22 | 100 | 2 | 2 | 2
(4 rows)
Time: 0.737 ms
POC=# select age,salary,
POC-# AVG(salary) over(partition by age) as "年齢毎の平均給与"
POC-# from members;
age | salary | 年齢毎の平均給与
-----+--------+----------------------
20 | 100 | 100.0000000000000000
21 | 101 | 101.0000000000000000
22 | 102 | 101.0000000000000000
22 | 100 | 101.0000000000000000
(4 rows)
Time: 1.150 ms
MySQL
- Windows Functionで集計
mysql> WITH Sales AS (
-> SELECT 123 AS sku, 1 AS day, 9.99 AS price UNION ALL
-> SELECT 123, 1, 8.99 UNION ALL
-> SELECT 123, 2, 9.99 UNION ALL
-> SELECT 789, 3, 1.00 UNION ALL
-> SELECT 456, 3, 4.25 UNION ALL
-> SELECT 789, 3, 0.99
-> )
-> SELECT
-> sku,
-> day,
-> SUM(price) over (partition by sku,day) as "total" from Sales;
+-----+-----+-------+
| sku | day | total |
+-----+-----+-------+
| 123 | 1 | 18.98 |
| 123 | 1 | 18.98 |
| 123 | 2 | 9.99 |
| 456 | 3 | 4.25 |
| 789 | 3 | 1.99 |
| 789 | 3 | 1.99 |
+-----+-----+-------+
6 rows in set (0.00 sec)
- Group by +Rollupで小計を追加
mysql> WITH Sales AS (
SELECT 123 AS sku, 1 AS day, 9.99 AS price UNION ALL
SELECT 123, 1, 8.99 UNION ALL
SELECT 456, 1, 4.56 UNION ALL
SELECT 123, 2, 9.99 UNION ALL
SELECT 789, 3, 1.00 UNION ALL
SELECT 456, 3, 4.25 UNION ALL
SELECT 789, 3, 0.99
)
SELECT
sku,
day,
SUM(price) AS total
FROM Sales GROUP by sku, day WITH ROLLUP
ORDER BY sku, day;
+------+------+-------+
| sku | day | total |
+------+------+-------+
| 123 | 1 | 18.98 |
| 123 | 2 | 9.99 |
| 123 | NULL | 28.97 |
| 456 | 1 | 4.56 |
| 456 | 3 | 4.25 |
| 456 | NULL | 8.81 |
| 789 | 3 | 1.99 |
| 789 | NULL | 1.99 |
| NULL | NULL | 39.77 |
+------+------+-------+
9 rows in set (0.01 sec)
mysql>
Explain

備考:シンプルに総合計を計算する場合も便利です。
以前は、MySQL では、WITH ROLLUP オプションを持つクエリーで DISTINCT または ORDER BY を使用できませんでした。 この制限は、MySQL 8.0.12 以降ではなくなりました。 (Bug #87450、Bug #86311、Bug #26640100、Bug #26073513)
12.20.2 GROUP BY 修飾子
mysql> select * from Table_A;
+----+-----------+
| id | system_id |
+----+-----------+
| 1 | -1 |
| 2 | -1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 3 |
| 6 | 4 |
| 7 | 5 |
| 8 | 6 |
| 9 | 7 |
| 10 | 8 |
+----+-----------+
10 rows in set (0.00 sec)
mysql> select system_id,count(system_id) from Table_A group by system_id;
+-----------+------------------+
| system_id | count(system_id) |
+-----------+------------------+
| -1 | 2 |
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 1 |
| 7 | 1 |
| 8 | 1 |
+-----------+------------------+
9 rows in set (0.00 sec)
mysql> select system_id,count(system_id) from Table_A group by system_id with rollup;
+-----------+------------------+
| system_id | count(system_id) |
+-----------+------------------+
| -1 | 2 |
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 1 |
| 7 | 1 |
| 8 | 1 |
| NULL | 10 |
+-----------+------------------+
10 rows in set (0.01 sec)
mysql>
備考:MySQLにおけるWindows関数の処理1
mysql> select * from members limit 10;
+----+------+-----+--------+------------+
| id | name | age | salary | regist |
+----+------+-----+--------+------------+
| 1 | Mr.T | 20 | 100 | 2022-03-03 |
| 2 | Mr.U | 21 | 101 | 2022-03-03 |
| 3 | Mr.V | 22 | 102 | 2022-03-03 |
| 4 | Mr.W | 23 | 90 | 2022-03-03 |
| 5 | Mr.X | 22 | 100 | 2022-04-01 |
+----+------+-----+--------+------------+
5 rows in set (0.00 sec)
mysql> select id,name,age,salary,Row_Number() over(partition by age order by salary desc) as "row_number",
-> rank() over(partition by age order by salary desc) as "rank",
-> dense_rank() over(partition by age order by salary desc) as "dense_rank"
-> from members;
+----+------+-----+--------+------------+------+------------+
| id | name | age | salary | row_number | rank | dense_rank |
+----+------+-----+--------+------------+------+------------+
| 1 | Mr.T | 20 | 100 | 1 | 1 | 1 |
| 2 | Mr.U | 21 | 101 | 1 | 1 | 1 |
| 3 | Mr.V | 22 | 102 | 1 | 1 | 1 |
| 5 | Mr.X | 22 | 100 | 2 | 2 | 2 |
| 4 | Mr.W | 23 | 90 | 1 | 1 | 1 |
+----+------+-----+--------+------------+------+------------+
5 rows in set (0.00 sec)
mysql> select age,salary,
-> AVG(salary) over(partition by age) as "年齢毎の平均給与"
-> from members;
+-----+--------+--------------------------+
| age | salary | 年齢毎の平均給与 |
+-----+--------+--------------------------+
| 20 | 100 | 100.0000 |
| 21 | 101 | 101.0000 |
| 22 | 102 | 101.0000 |
| 22 | 100 | 101.0000 |
| 23 | 90 | 90.0000 |
+-----+--------+--------------------------+
5 rows in set (0.00 sec)
備考:MySQLにおけるWindows関数の処理2
mysql> WITH RECURSIVE CTE AS
-> (SELECT 1 AS a UNION ALL SELECT 1+a FROM CTE WHERE a<10)
-> SELECT * FROM CTE;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+------+
10 rows in set (0.00 sec)
Windows Functionに関して
参照:grouping function for Group by rollup in bigquery standard