1. Introduction to Grouping Query
Grouping query is to group the query results according to the specified fields. The data in the fields are equally divided into one group.
Grouping query syntax format:
GROUP BY column name [HAVING conditional expression] [WITH ROLLUP]
explain:
① Column Name: refers to grouping according to the specified field value.
② HAVING: condition expression used to filter grouped data.
③ , WITH ROLLUP: add a record at the end of all records to display the statistics and calculation results of aggregate function during select query.
2. Use of GROUP BY
GROUP BY can be used for grouping a single field or multiple fields.
Example: Group by gender field
SELECT gender FROM `students` GROUP BY `gender` ;
Example: Grouping by name and gender
SELECT name,gender FROM `students` GROUP BY name,gender ;
3、GROUP BY GROUP_ CONCAT()
GROUP_ CONCAT (field name): counts the information set of each group's specified field, and each information is separated by a comma.
Example: Query the names of all male students and all female students in the student table
SELECT gender, GROUP_ CONCAT(name) FROM `students` GROUP BY `gender` ;
4. Use of the GROUP BY aggregate function
Example: count the number of boys and girls in the student table
SELECT gender, COUNT( *) FROM `students` GROUP BY `gender` ;
5. Use of GROUP BY HAVING
HAVING is similar to WHERE in that it filters data, but HAVING filters grouping data and can only be used for GROUP BY.
Example: count the number of boys and girls in the student table. If the number of boys or girls is less than 6, the group will not be counted
SELECT gender, COUNT( *) FROM `students` GROUP BY `gender` HAVING COUNT( *) >6;
6. Use of GROUP BY WITH ROLLUP
With ROLLUP is used to add a new line after the last record to display the statistics and calculation results of the aggregate function during the select query.
Example: Count the number of boys and girls in the student table, and make a summary record of the total number of boys and girls at the end
SELECT gender, COUNT( *) FROM `students` GROUP BY `gender` WITH ROLLUP ;
