WITHROLLUP is an interesting application in MySQL's extended SQL statement. Based on the grouping statistics, the same statistics (SUM, AVG, COUNThellip;) are performed.
MySQL extended SQL has a very interesting application WITH ROLLUP. The same statistics (SUM, AVG, COUNThellip;) are performed based on the grouping statistics.
MySQL's extended SQL has a very interesting application WITH ROLLUP. The same statistics (SUM, AVG, COUNT…) are performed based on the grouping statistics ...), Similar to the statistical functions in Oracle, Oracle's statistical functions are more powerful.
The following shows the total mileage and average mileage of a single driver and all drivers:
Mysql> select name, sum (miles) as 'miles/driver'
-> From driver_log group by name with rollup;
+ ------- + -------------- +
| Name | miles/driver |
+ ------- + -------------- +
| Ben| 362 |
| Henry | 1, 911 |
| Suzi| 893 |
| NULL | 2166 |
+ ------- + -------------- +
4 rows in set (0.00 sec)
Mysql> select name, avg (miles) as driver_avg
-> From driver_log group by name with rollup;
+ ------- + ------------ +
| Name | driver_avg |
+ ------- + ------------ +
| Ben| 120.6667 |
| Henry | 1, 182.2000 |
| Suzi| 446.5000 |
| NULL | 216.6000 |
+ ------- + ------------ +
4 rows in set (0.00 sec)
Mysql> select name, sum (miles) as 'miles/driver ', avg (miles) as driver_avg
-> From driver_log group by name with rollup;
+ ------- + -------------- + ------------ +
| Name | miles/driver | driver_avg |
+ ------- + -------------- + ------------ +
| Ben| 362 | 120.6667 |
| Henry | 911 | 182.2000 |
| Suzi| 893 | 446.5000 |
| NULL | 2166/216.6000 |
+ ------- + -------------- + ------------ +
4 rows in set (0.00 sec)
With rollup is also valid in multiple groups:
Mysql> select srcuser, dstuser, count (*) from mail group by srcuser, dstuser;
+ --------- + ---------- +
| Srcuser | dstuser | count (*) |
+ --------- + ---------- +
| Barb | 1 |
| Barb | tricia | 2 |
| Gene | barb | 2 |
| Gene | 3 |
| Gene | tricia | 1 |
| Phil | barb | 1 |
| Phil | 2 |
| Phil | tricia | 2 |
| Tricia | gene | 1 |
| Tricia | phil | 1 |
+ --------- + ---------- +
10 rows in set (0.05 sec)
Mysql> select srcuser, dstuser, count (*) from mail group by srcuser, dstuser with rollup;
+ --------- + ---------- +
| Srcuser | dstuser | count (*) |
+ --------- + ---------- +
| Barb | 1 |
| Barb | tricia | 2 |
| Barb | NULL | 3 |
| Gene | barb | 2 |
| Gene | 3 |
| Gene | tricia | 1 |
| Gene | NULL | 6 |
| Phil | barb | 1 |
| Phil | 2 |
| Phil | tricia | 2 |
| Phil | NULL | 5 |
| Tricia | gene | 1 |
| Tricia | phil | 1 |
| Tricia | NULL | 2 |
| NULL | 16 |
+ --------- + ---------- +
15 rows in set (0.00 sec)
This article permanently updates the link address: