MySQL uses WITH ROLLUP instead of ROLLUP .

SELECT country, product, sum(profit) FROM sales GROUP BY country, product WITH ROLLUP;

Before applying ROLLUP (left) vs ROLLUP applied (right)

country	product	sum (profit)		country	product	sum (profit)
Finland	Computer	1500		Finland	Computer	1500
Finland	Phone	110		Finland	Phone	110
India	Calculator	150		Finland	NULL	1610
India	Computer	1200		India	Calculator	150
USA	Calculator	125		India	Computer	1200
USA	Computer	4200		India	NULL	1350
USA	TV	250		USA	Calculator	125
				USA	Computer	4200
				USA	TV	250
				USA	NULL	4575
				NULL	NULL	7535

ROLLUP replaces the aggregated reference value with a NULL value. With COALESCE you can put whatever text you want.

(Can be replaced with IFNULL in MySQL)

SELECT COALESCE(country,"ALL countries") as country, 
       COALESCE(product,"ALL products") as product, 
       sum(profit) FROM sales GROUP BY country, product WITH ROLLUP;

country	product	sum (profit)
Finland	Computer	1500
Finland	Phone	110
Finland	ALL products	1610
India	Calculator	150
India	Computer	1200
India	ALL products	1350
USA	Calculator	125
USA	Computer	4200
USA	TV	250
USA	ALL products	4575
ALL countries	ALL products	7535

Reference:

As of MySQL 8.0 , you can use the GROUPING function like Oracle . The result is the same as the query with IFNULL.

The GROUPING value returns 1 or 0 at the ROW where the aggregate should be placed (where NULL is indicated).

The IF statement allows you to replace that point with another character.

SELECT IF(GROUPING(country),'ALL countries',country), 
       IF(GROUPING(product),'ALL products',country), SUM(profit) 
FROM sales GROUP BY country, product WITH ROLLUP;

Getting Advanced Row Counts in MySQL

 In today’s follow-up, we’ll use the COUNT() function in more sophisticated ways to tally unique values as well as those which satisfy a condition.

Distinct Counts

The COUNT(DISTINCT) function returns the number of rows with unique non-NULL values. Hence, the inclusion of the DISTINCT keyword eliminates duplicate rows from the count. Its syntax is:

COUNT(DISTINCT expr,[expr...])

As with the regular COUNT() function, the expr parameters above can be any given expression, including specific columns, all columns (*), function return values, or expression such as IF/CASE statements.

A Simple Example

Say that we had the following table of clients:

+------------+-------------+
| last_name  | first_name  |
+------------+-------------+
| Tannen     | Biff        |
+------------+-------------+
| McFly      | Marty       |
+------------+-------------+
| Brown      | Dr. Emmett  |
+------------+-------------+
| McFly      | George      |
+------------+-------------+

Invoking COUNT(*) will return the number of all rows (4) while a COUNT DISTINCT on the last_name will count each row with a duplicated last name as one, so that we get a total of 3:

SELECT COUNT(*), COUNT(DISTINCT last_name) FROM clients; 
+----------+---------------------------+
| COUNT(*) | COUNT(DISTINCT last_name) |
+----------+---------------------------+
| 4        | 3                         |
+----------+---------------------------+

Conditional Counts using Expressions

As mentioned above, COUNT() function parameters are not limited to column names; function return values and expressions such as IF/CASE statements are also fair game.

Here’s a table that contains several users’ telephone numbers and sex (limited to two for simplicity):

+------------+---------+
| tel        | sex     |
+------------+---------+
| 7136609221 | male    |
+------------+---------+
| 7136609222 | male    |
+------------+---------+
| 7136609223 | female  |
+------------+---------+
| 7136609228 | male    |
+------------+---------+
| 7136609222 | male    |
+------------+---------+
| 7136609223 | female  |
+------------+---------+

Say that we wanted to build a query that told us how many distinct women and men there are in the table. The person is identified by their telephone ('tel') number. It is possible for the same 'tel' to appear multiple times, but that tel’s gender should only be counted one time.

Here's one option using a separate COUNT DISTINCT for each column:

SELECT COUNT(DISTINCT tel) gender_count,
       COUNT(DISTINCT CASE WHEN gender = 'male'   THEN tel END) male_count,
       COUNT(DISTINCT CASE WHEN gender = 'female' THEN tel END) female_count
FROM people

This SELECT statement would yield the following:

+--------------+------------+---------------+
| gender_count | male_count | female_count  |
+--------------+------------+---------------+
| 4            | 3          | 1             |
+--------------+------------+---------------+

BONUS! Grouping and Including a Grand Total

You can also stack counts vertically using GROUP BY:

+---------+-------+
| GroupId | Count |
+---------+-------+
| 1       | 5     |
+---------+-------+
| 2       | 4     |
+---------+-------+
| 3       | 7     |
+---------+-------+
| Total:  | 11    |
+---------+-------+

The “Total:” was produced using the SQL GROUPING() function, which was added in MySQL 8.0.1. It distinguishes between a NULL representing the set of all values in a super-aggregate row (produced by a ROLLUP) from a NULL in a regular row.

Here’s the full SQL:

Select  Case When Grouping(GroupId) = 1
             Then 'Total:'
             Else GroupId
        End As GroupId,
        Count(*) Count
From    user_groups
Group By GroupId With Rollup
Order By Grouping(GroupId), GroupId

MYSQL ROLLUP

Summary: in this tutorial, you will learn how to use the SQL ROLLUP to generate multiple grouping sets.

Introduction to SQL ROLLUP

The ROLLUP is an extension of the GROUP BY clause. The ROLLUP option allows you to include extra rows that represent the subtotals, which are commonly referred to as super-aggregate rows, along with the grand total row. By using the ROLLUP option, you can use a single query to generate multiple grouping sets.

Note that a grouping set is a set of columns by which you group. For example, a query that returns the inventory by the warehouse,  the grouping set is (warehouse).

1 2 3 4 5 6 7

SELECT     warehouse,     SUM (quantity) qty FROM     inventory GROUP BY     warehouse;

For more information about the GROUPING SETS, check it out the grouping sets tutorial.

The following illustrates the basic syntax of the SQL ROLLUP:

1 2 3 4 5

SELECT     c1, c2, aggregate_function(c3) FROM     table GROUP BY ROLLUP (c1, c2);

The ROLLUP assumes a hierarchy among the input columns. For example, if the input column is (c1,c2), the hierarchy c1 > c2. The ROLLUP generates all grouping sets that make sense considering this hierarchy. This is why we often use ROLLUP to generate the subtotals and the grand total for reporting purposes.

In the syntax above, ROLLUP(c1,c2) generates three following grouping sets:

1 2 3

(c1,c2) (c1) ()

This syntax is supported by Oracle, Microsoft SQL Server, and PostgreSQL. However, MySQL has a slightly different syntax as shown below:

1 2 3 4 5

SELECT     c1, c2, aggregate_function(c3) FROM     table_name GROUP BY c1, c2 WITH ROLLUP;

SQL ROLLUP examples

We will use the inventory table that we set up in the GROUPING SETS tutorial for the demonstration.

SQL ROLLUP with one column example

The following statement uses the GROUP BY clause and the SUM() function to find the total inventory by warehouse:

1 2 3 4 5

SELECT     warehouse, SUM(quantity) FROM     inventory GROUP BY warehouse;

To retrieve the total products in all warehouses, you add the ROLLUP to the GROUP BY clause as follows:

1 2 3 4 5

SELECT     warehouse, SUM(quantity) FROM     inventory GROUP BY ROLLUP (warehouse);

As you can see in the result, the NULL value in the warehouse column specifies the grand total super-aggregate line. In this example, the ROLLUP option causes the query to produce another row that shows the total products in all warehouses.

To make the output more readable, you can use the COALESCE() function to substitute the NULL value by the All warehouses as follows:

1 2 3 4 5 6

SELECT     COALESCE(warehouse, 'All warehouses') AS warehouse,     SUM(quantity) FROM     inventory GROUP BY ROLLUP (warehouse);

SQL ROLLUP with multiple columns example

The following statement calculates the inventory by warehouse and product:

1 2 3 4 5

SELECT     warehouse, product, SUM(quantity) FROM     inventory GROUP BY warehouse, product;

Let’s add the ROLLUP to the GROUP BY clause:

1 2 3 4 5

SELECT     warehouse, product, SUM(quantity) FROM     inventory GROUP BY ROLLUP (warehouse , product);

Note that the output consists of summary information at two levels of analysis, not just one:

• Following each set of product rows for a specified warehouse, an extra summary row appears displaying the total inventory. In these rows, values in the productcolumn set to NULL.
• Following all rows, an extra summary row appears showing the total inventory of all warehouses and products. In these rows, the values in the  warehouse and product columns set to NULL.

SQL ROLLUP with partial rollup example

You can use ROLLUP to perform a partial roll-up that reduces the number of subtotals calculated as shown in the following example:

1 2 3 4 5

SELECT     warehouse, product, SUM(quantity) FROM     inventory GROUP BY warehouse, ROLLUP (product);

In this example, the ROLLUP only makes a supper-aggregate summary for the product column, not the warehouse column.

In this tutorial, you have learned how to use the SQL ROLLUP to perform multiple levels of analysis with a single query.