Introduction to MySQL advanced queries and Group By collections

1 GROUP_CONCAT

mysql > SELECT student_name,
- > GROUP_CONCAT(test_score)
- > FROM student
- > GROUP BY student_name;

Or:

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 concatenation value of a combination of expressions. You can delete duplicate values using DISTINCT. If you want multiple result values sorted, you should use the ORDER BY clause. To do this in reverse order, add the DESC (descending) keyword to the column name you want to sort with the ORDER BY clause. The default order is ascending; It can be specified explicitly using ASC. SEPARATOR is followed by the string value in the middle of the value that should be inserted into the result. The default is comma (','). You can remove all delimiters by specifying SEPARATOR ".

PS: is a string that can be obtained in one statement with one combination of each subvalue of the item GROUP BY is aggregated into

2 WITH ROLLUP

The GROUP BY clause allows one to add an extra line to the abbreviated WITH ROLLUP modifier. These lines represent high-level (or high-aggregation) shorthand operations. ROLLUP thus allows you to answer questions about the inquiry from a multi-layer analytical perspective

Or you can use ROLLUP, which provides a two-tier analysis with one query. Adding an WITH ROLLUP modifier to the GROUP BY statement causes the query to produce another line of results that shows the total value of all the years:

mysql > SELECT year, SUM(profit) FROM sales GROUP BY year WITH ROLLUP;

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

| year | SUM(profit) |

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

| 2000 | 4525 |

| 2001 | 3010 |

| NULL | 7535 |

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

The aggregate high aggregation row is indicated by the NULL value in the year column.

When you have multiple GROUP BY columns, ROLLUP produces a more complex effect. At this point, each time an "break" (value change) appears in any column other than the last category column, the query produces a high-aggregation cumulative row.

For example, in the absence of ROLLUP, a table 1 of the sales table based on year, country, and product might look like this:

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 year/country/product analysis level. When ROLLUP is added, the query generates 1 additional line:

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 cannot use the ORDER BY clause at the same time to sort the results. In other words, ROLLUP and ORDER BY are mutually exclusive. However, you can still have some control over the sort. In MySQL, GROUP BY sorts the results, and you can sort individual columns by using the specific ASC and DESC keywords in the GROUP BY list. The higher-level aggregate rows added by ROLLUP still appear after the rows they were calculated for anyway.

LIMIT can be used to limit the number of lines returned to the client. LIMIT is used after ROLLUP, so this restriction cancels the lines added by ROLLUP