Detailed explanation of the group by statement in MySQL database group query

1: Statement order of grouping function

   1 SELECT
    ...
   2 FROM
   ...
   3 WHERE
   ...
   4 GROUP BY
   ...
   5 HAVING
   ...
   6 ORDER BY
   ...

2. The difference between WHERE and HAVING filter conditions

                  Data source location keyword WHERE Original table ORDER BY statement before WHERE
  
  HAVING Grouped result set after ORDER BY statement HAVING

Three: Examples

#1. Query the maximum age of students in each classSELECT 
       MAX(age),class
  FROM
       STU_CLASS
  GROUP BY CLASS;
# 2. Query the maximum age of male and female students in each class SELECT
         MAX(age), class, sex
  FROM
        STU_CLASS
   GROUP BY
        class,sex;
#3. Query the average age of boys and girls in each class, and the average age is greater than 15 years old SELECT
       AVG (age), class, sex
FROM 
       STU_CLASS
GROUP BY
       class,sex
 HAVING
        avg(age) > 15;

Four: Note

1. Usually, the GROUP BY keyword is used together with an aggregate function. First, the GROUP BY keyword is used to group the records, and then the aggregate function is used to calculate each group. The GROUP BY keyword and aggregate functions are often needed when performing statistics.

COUNT() function: used to count the number of records.
SUM() function: used to calculate the sum of the values ​​of a field.
AVG() function: used to calculate the average value of a field.
MAX() function: used to query the maximum value of a field.
MIN() function: used to query the minimum value of a field.

The above is the detailed content of the detailed explanation of the group by statement of MySQL database group query. For more information about MySQL database statements, please pay attention to other related articles on 123WORDPRESS.COM!