Mysql group by with rollup
1. Common group by operations can be grouped BY department and position to calculate the average salary of each department and position: mysql> select dep, pos, avg (sal) from employee group by dep, pos; + ------ + ----------- + | dep | pos | avg (sal) | + ------ + ----------- + | 01 | 01 | 1500.0000 | 01 | 02 | 1950.0000 | 02 | 01 | 1500.0000 | 02 | 02 | 2450.0000 | 03 | 01 | 2500.0000 | 03 | 02 | 2550.0000 | + ------ + ----------- + 6 rows in set (0.02 sec) 2. If we want to display the average value of the Department and the average value of all employees Average value. A common group by statement cannot be implemented. You need to perform another query operation or use a program to calculate the value. If you use a group by statement WITH a ROLLUP clause, you can easily implement this requirement: mysql> select dep, pos, avg (sal) from employee group by dep, pos with rollup; + ------ + ----------- + | dep | pos | avg (sal) | + ------ + ----------- + | 01 | 01 | 1500.0000 | 01 | 02 | 1950.0000 | 01 | NULL | 1725.0000 | 02 | 01 | 1500.0000 | 02 | 02 | | 2450.0000 | 02 | NULL | 2133.3333 | 03 | 01 | 2500.0000 | 03 | 02 | 2550.0000 | 03 | NULL | 2533.3333 | NULL | 2090.0000 | + ------ + ------ + ----------- + 10 rows in set (0.00 sec)