MYSQL API with ROLLUP

The GROUP by clause allows an additional row to be added to the abbreviated output with the ROLLUP modifier. These lines represent high-level (or high-aggregation) shorthand operations. ROLLUP thus allows you to answer questions about inquiries in the context of multi-layered analysis. For example, it can be used to provide support for OLAP (online analytical Processing) operations.

Imagine a table named sales that has a profit column for year, country, product, and record sales profit:

CREATE TABLE Sales

(

Year INT is not NULL,

Country VARCHAR () not NULL,

Product VARCHAR (+) is not NULL,

Profit INT

);

You can use this simple group by to summarize the contents of the table once a year:

Mysql> SELECT Year, SUM (profit) from the sales GROUP by year;

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

| Year | SUM (Profit) |

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

|        2000 | 4525 |

|        2001 | 3010 |

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

This output shows the total profit for each year, but if you also want to determine the total profit for all years, you must accumulate the individual values for each year or run an addition query yourself.

Or you can use ROLLUP, which can provide double analysis with a single query. Adds a with rollup modifier to the group BY statement, which causes the query to result in a different row that shows the total value of all years:

Mysql> SELECT Year, SUM (profit) from the sales GROUP by year with ROLLUP;

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

| Year | SUM (Profit) |

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

|        2000 | 4525 |

|        2001 | 3010 |

|        NULL | 7535 |

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

A total high aggregation row is marked with a null value in the Year column.

When there are multiple GROUP by columns, the effect of rollup is more complex. At this point, each time a "break" (a change in value) occurs in any column except for the last categorical column, the inquiry produces a high-aggregation cumulative row.

For example, in the absence of rollup, a list of sales tables based on year, country, and product might look like the following:

Mysql> SELECT Year, country, product, SUM (profit)

-From sales

- GROUP by year, country, product;

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

| Year | Country | Product | SUM (Profit) |

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

| 2000 | Finland |        Computer | 1500 |

| 2000 | Finland |         Phone | 100 |

| 2000 | India |         Calculator | 150 |

| 2000 | India |        Computer | 1200 |

| 2000 | USA |          Calculator | 75 |

| 2000 | USA |        Computer | 1500 |

| 2001 | Finland |          Phone | 10 |

| 2001 | USA |          Calculator | 50 |

| 2001 | USA |        Computer | 2700 |

| 2001 | USA |         TV | 250 |

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

The output that represents the total value is only at the annual/country/product analysis level. When rollup is added, the query generates some extra rows:

Mysql> SELECT Year, country, product, SUM (profit)

-From sales

- GROUP by year, country, product with ROLLUP;

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

| Year | Country | Product | SUM (Profit) |

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

| 2000 | Finland |        Computer | 1500 |

| 2000 | Finland |         Phone | 100 |

| 2000 | Finland |        NULL | 1600 |

| 2000 | India |         Calculator | 150 |

| 2000 | India |        Computer | 1200 |

| 2000 | India |        NULL | 1350 |

| 2000 | USA |          Calculator | 75 |

| 2000 | USA |        Computer | 1500 |

| 2000 | USA |        NULL | 1575 |

| 2000 | NULL |        NULL | 4525 |

| 2001 | Finland |          Phone | 10 |

| 2001 | Finland |          NULL | 10 |

| 2001 | USA |          Calculator | 50 |

| 2001 | USA |        Computer | 2700 |

| 2001 | USA |         TV | 250 |

| 2001 | USA |        NULL | 3000 |

| 2001 | NULL |        NULL | 3010 |

| NULL | NULL |        NULL | 7535 |

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

For this inquiry, add the ROLLUP clause so that the village output contains a four-layer analysis of the brief information, not just one of the following is how to interpret the rollup output:

• After each set of product lines for a given set of years and countries, an additional total row is generated, showing the total value of all products. These lines set the product column to NULL.
• After a set of rows for a given year, an additional total row is generated, showing the total value of all countries and products. These lines are set to NULL for the country and product columns.
• Finally, after all other lines, an additional total column is generated that shows the total value of all years, countries, and products. This line is set to NULL for the year, country, and product columns.

Other precautions when using the rollup

The following items list some of the special states of MySQL execution rollup:

When you use rollup, you cannot use the ORDER BY clause to sort results at the same time. In other words, ROLLUP and order by are mutually exclusive. However, you can still have some control over the sort. In MySQL, group by can sort the results, and you can sort individual columns by using explicit ASC and DESC keywords in the columns specified by the GROUP by list. (regardless of how the higher-level totals rows that are added by rollup still appear after the rows they are calculated from).

MYSQL API with ROLLUP