MySQL Modify GROUP by instance

GROUP by Modify Program
。

Imagine a table named sales with a profit column for the year, country, product, and record sales profit:

CREATE TABLE www.111cn.net

(

Year INT is not NULL,

Country VARCHAR is not NULL,

Product VARCHAR not NULL,

Profit INT

);

You can use this simple group by to make a summary of the contents of a table each year:

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

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

| Year | SUM (Profit) |

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

|        2000 | 4525 |

|        2001 | 3010 |

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

This output shows the total profit per year, but if you also want to determine the total profit for all years, you have to add up a single value per year or run an addition query.

Or you can use ROLLUP, which provides a double analysis with a single query. Adds a with rollup modifier to the group by statement, causing the query to produce another line of results 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 clustered row is marked with a null value in the Year column.

Rollup produces more complexity when there are multiple GROUP by columns. At this point, each time a "break" (value change) occurs in any column other than the last category column, the inquiry produces a high aggregated cumulative row.

For example, in the absence of rollup, a list of sales tables based on year, country, and product 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 |

The output of the total value is only at the analysis level of the year/country/product. When rollup is added, the query produces some extra lines:

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 |

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

For this enquiry, add the ROLLUP clause so that the village output contains a brief message for the four-tier analysis, not just one of the following explains the rollup output:

After each set of product lines for a given year and country, an additional total row is generated, showing the total value of all products. These rows 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 rows state and product columns are set to NULL.
Finally, after all the other rows, an additional total column is generated, showing the total value of all years, countries, and products. This row sets the year, country, and product columns to NULL.