With rollup in MySQL

With rollup in MySQL

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: