Mysql advanced Query and group by collection use introduction _mysql

1 group_concat

Mysql> SELECT Student_name,
-> Group_concat (Test_score)
-> from Student
-> GROUP by Student_name;

Pre

Mysql> SELECT Student_name,
-> Group_concat (DISTINCT test_score
-> ORDER by Test_score DESC SEPARATOR ')
-> from Student
-> GROUP by Student_name;

In MySQL, you can get the join value of an expression combination. You can use distinct to delete duplicate values. If you want multiple result values to be sorted, you should use the ORDER BY clause. To be in reverse order, add the DESC (descending) keyword to the name of the column you want to sort by the ORDER BY clause.   The default order is ascending, and you can use ASC to specify it explicitly. SEPARATOR follows the string value in the middle of the value that should be inserted into the result. The default is comma (', '). By specifying separator ', you can remove all delimiters.

PS: is a string that can be used in a single statement to get a combination of each of the child values of a group by the aggregated item

2 with ROLLUP

The GROUP by clause allows an additional row to be added to the abbreviated output end 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-tier analysis

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 |

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

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

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

When you use rollup, you can't sort the results by using the ORDER BY clause 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. (no matter how you sort the higher-level totals rows added by rollup still appear after the rows that they are calculated in).

Limit can be used to limit the number of rows returned to the client. LIMIT is used behind rollup, so this restriction cancels the rows added by rollup