Mysql group by with rollup, mysqlrollup

Mysql group by with rollup, mysqlrollup

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)