MySQL aggregate function sorting

MySQL result sorting - Aggregate functions

environment
Query result sorting
Grouping and Aggregation of Queries
Check the average age of students
Check the total number of people
Find out how many people are of each age.
Find out the maximum age

Summarize

MySQL result sorting - Aggregate functions

environment

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'student number',
  `student_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Student name',
  `sex` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Gender\r\n',
  `age` int(11) NULL DEFAULT NULL COMMENT 'Age',
  `result` double(10, 0) NULL DEFAULT NULL COMMENT 'Results',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `student` VALUES (1, '小王', '男', 18, 90);
INSERT INTO `student` VALUES (2, 'Xiao Li', 'Female', 19, 80);
INSERT INTO `student` VALUES (3, 'Xiao Ming', 'Male', 20, 85);
INSERT INTO `student` VALUES (4, '小张', '男', 21, 87);

Query result sorting

The most common application is to filter popular products or hot searches on Weibo.

Syntax format:

SELECT field name 1… FROM table name ORDER BY field name 1 [ASC | DESC ], field name 2 [ASC | DESC ]…;

Field names 1 and 2 are the basis for sorting the query results. ASC means ascending order and DESC means descending order. The default is ASC.

Raise your paw:

SELECT * FROM student ORDER BY age DESC ;

When followed by two sorting rules, the first field name is the same and the sorting is based on the second field name sorting rule.

All you have to do is figure it out on your own.

think:

How to write the function to search for age in descending order and student_name in ascending order?

Grouping and Aggregation of Queries

Aggregate functions

function	effect
AVG()	Returns the average (mean) of a column
COUNT()	Returns the number of rows in a column (statistics)
MAX()	Returns the maximum value (max) of a column
MIN()	Returns the minimum value (minimum) in a column
SUM()	Returns the sum of the values in a column (sum)

Check the average age of students

select AVG(age) as "age" from student;

Check the total number of people

select count(id) as "total number of people" from student;

Find out how many people are of each age.

select age, count(id) as "total number of people" from student GROUP BY age;

insert image description here

Find out the maximum age

select MAX(age) as "Maximum age" from student;

[External link image transfer failed. The source site may have an anti-hotlink mechanism. It is recommended to save the image and upload it directly (img-dzr9FGai-1619187251369) (8mysql_result sorting_aggregation function.assets/image-20210423220055446.png)]

Less than the same.

Find out how many men and women there are

select sex ,count(*) AS "Number of people" from student GROUP BY sex;
#GROUP BY groups the results according to the following field names

How much is the total score of the query?

select sum(result) as "total score" FROM student;

[External link image transfer failed. The source site may have an anti-hotlink mechanism. It is recommended to save the image and upload it directly (img-BcxPQPRB-1619187251372) (8mysql_result sorting_aggregation function.assets/image-20210423221101057.png)]