MYSQL with ROLLUP

With the group by, the with ROLLUP sentence can be used to retrieve more packet aggregation information, which not only can retrieve the aggregated information of each group as the general group by statement, but also can retrieve the aggregate information of the group class.

The following example compares the common group by operation with the group by operation with the ROLLUP clause:

The content of the query table is the employee's basic information table:

[XHTML]View PlainCopy < param name= "wmode" value= "Transparent" >

2. <a href="HTTP://LIB.CSDN.NET/BASE/14" class=' Replace_word ' title="undefined" target= '_blank ' style=' color: #df3434; font-weight:bold; '  >mysql</a>> select * from Employee;
4. +------+--------+------+------+------+
6. | ID | name | DEP | POS | Sal |
8. +------+--------+------+------+------+
10. | 1 | ABCD | 01 | 01 | 1000 |
12. | 2 | Eefs | 01 | 02 | 2000 |
14. | 3 | Micro | 02 | 01 | 1500 |
16. | 4 | Cathey | 02 | 02 | 3000 |
18. | 5 | Amy | 03 | 01 | 2500 |
20. | 6 | Lily | 03 | 02 | 2200 |
22. | 7 | Bobo | 01 | 01 | 2000 |
24. | 8 | Gray | 01 | 02 | 1900 |
26. | 9 | Leon | 03 | 02 | 2900 |
28. | 10 | Sun | 02 | 02 | 1900 |
30. +------+--------+------+------+------+
32. Rows in Set (0.00 sec)

A common group by operation that can be grouped by department and position to calculate the average wage for each department, each position:

[XHTML]View PlainCopy

2. MySQL> select Dep,pos,avg (SAL) from the employee group by Dep,pos;
4. +------+------+-----------+
6. | DEP | POS | AVG (SAL) |
8. +------+------+-----------+
10. | 01 | 01 | 1500.0000 |
12. | 01 | 02 | 1950.0000 |
14. | 02 | 01 | 1500.0000 |
16. | 02 | 02 | 2450.0000 |
18. | 03 | 01 | 2500.0000 |
20. | 03 | 02 | 2550.0000 |
22. +------+------+-----------+
24. 6 rows in Set (0.02 sec)

If we want to show the average of the department and the average of all employees again, the normal GROUP by statement is not possible, and you need to perform a separate query operation or a program to calculate. If you use the GROUP by statement with the WITH ROLLUP clause, you can easily implement this requirement:

[Java]View PlainCopy

2. Mysql> Select Dep,pos,avg (SAL) from the employee GROUP by Dep,pos with rollup;
4. +------+------+-----------+
6. | DEP | POS | AVG (SAL) |
8. +------+------+-----------+
10. | 01 | 01 |  1500.0000 |
12. | 01 | 02 |  1950.0000 |
14. | 01 | NULL |  1725.0000 |
16. | 02 | 01 |  1500.0000 |
18. | 02 | 02 |  2450.0000 |
20. | 02 | NULL |  2133.3333 |
22. | 03 | 01 |  2500.0000 |
24. | 03 | 02 |  2550.0000 |
26. | 03 | NULL |  2533.3333 |
28. | NULL | NULL |  2090.0000 |
30. +------+------+-----------+
32. Rows in Set (0.00 sec)

It is important to note that when using a GROUP by statement with the WITH ROLLUP clause, the result set can no longer be sorted by using the order BY statement, and if the order of the returned results is not satisfactory, it needs to be sorted in the program after the application obtains the results.

(go) MYSQL with ROLLUP