WITHROLLUP of MYSQLAPI

WITHROLLUP of MYSQLAPI

The group by clause allows an additional row to be added to the simple output end WITH the ROLLUP modifier. These rows represent high-level (or high aggregation) simple operations. ROLLUP allows you to answer questions from the perspective of multi-layer analysis. For example, it can be used to support OLAP (Online Analytical Processing) operations.

Suppose a table named sales has a profit column of year, country, product, and sales profit record:

Create table sales

(

Year int not null,

Country VARCHAR (20) not null,

Product VARCHAR (32) not null,

Profit INT

);

You can use this simple group by statement to summarize the table content every year:

Mysql>SELECT year, SUM (profit) FROM sales group by year;

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

| Year | SUM (profit) |

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

| 2000/4525 |

| 2001/3010 |

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

This output shows the total annual profit, but if you want to determine the total profit for all years, you must accumulate a single value each year or run an addition query.

Alternatively, you can use ROLLUP, which provides a double-layer analysis with an inquiry. Add a modifier with rollup to the group by statement to generate another row of results for the query. This row shows the total value of all years:

Mysql>SELECT year, SUM (profit) FROM sales group by year with rollup;

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

| Year | SUM (profit) |

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

| 2000/4525 |

| 2001/3010 |

| NULL | 7535 |

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

The total high clustering rows are marked by the NULL value in the year column.

When multiple group by columns exist, ROLLUP produces more complex results. At this time, each time a "break" appears in any column except the last classification column (value change), the inquiry will generate a high clustering cumulative row.

For example, in the absence of ROLLUP, a yearly, country, and product-based sales table list may be as follows:

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 |

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

Indicates that the output result of the total value is only at the analysis level Of The Year/country/product. After ROLLUP is added, the query will generate some additional 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 | 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 | 3010 |

| Null| NULL | 7535 |

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

For this query, the ROLLUP clause is added to make the village output result contain brief information about the layer-4 analysis, not just how to explain the ROLLUP output below:

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

Other considerations when using ROLLUP

The following lists some special statuses for MySQL to execute ROLLUP:

When you use ROLLUP, you cannot use the order by clause to sort the results at the same time. In other words, ROLLUP and order by are mutually exclusive. However, you can still control the sorting. In MySQL, group by can sort the results, and you can use clear ASC and DESC keywords in the columns specified in the group by list to sort Individual columns. (No matter how you sort the high-level total rows added by ROLLUP, they still appear after the rows they are calculated ).