With ROLLUP in MySQL

There is a very interesting application with ROLLUP in MySQL's extended SQL, which is based on the statistical data of the group and then the same statistic (Sum,avg,count ... ), very similar to the functionality of statistical functions in Oracle, the statistical functions of Oracle are much 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 '

-Driver_log GROUP by name with rollup;

+-------+--------------+

| name | Miles/driver |

+-------+--------------+

|          Ben | 362 |

|          Henry | 911 |

|          Suzi | 893 |

|         NULL | 2166 |

+-------+--------------+

4 rows in Set (0.00 sec)

Mysql> Select Name,avg (miles) as Driver_avg

-Driver_log GROUP by name with rollup;

+-------+------------+

| name | Driver_avg |

+-------+------------+

|   Ben | 120.6667 |

|   Henry | 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

-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 under multiple groupings:

Mysql> Select Srcuser,dstuser,count (*) from the mail group by Srcuser,dstuser;

+---------+---------+----------+

| SrcUser | Dstuser | COUNT (*) |

+---------+---------+----------+

| Barb |        Barb | 1 |

| Barb |        Tricia | 2 |

| Gene |        Barb | 2 |

| Gene |        Gene | 3 |

| Gene |        Tricia | 1 |

| Phil |        Barb | 1 |

| Phil |        Phil | 2 |

| Phil |        Tricia | 2 |

| Tricia |        Gene | 1 |

| Tricia |        Phil | 1 |

+---------+---------+----------+

Rows in Set (0.05 sec)

Mysql> Select Srcuser,dstuser,count (*) from the Mail group by Srcuser,dstuser with rollup;

+---------+---------+----------+

| SrcUser | Dstuser | COUNT (*) |

+---------+---------+----------+

| Barb |        Barb | 1 |

| Barb |        Tricia | 2 |

| Barb |        NULL | 3 |

| Gene |        Barb | 2 |

| Gene |        Gene | 3 |

| Gene |        Tricia | 1 |

| Gene |        NULL | 6 |

| Phil |        Barb | 1 |

| Phil |        Phil | 2 |

| Phil |        Tricia | 2 |

| Phil |        NULL | 5 |

| Tricia |        Gene | 1 |

| Tricia |        Phil | 1 |

| Tricia |        NULL | 2 |

| NULL |       NULL | 16 |

+---------+---------+----------+

Rows in Set (0.00 sec)

With ROLLUP in MySQL