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

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!

You may also be interested in:
  • A brief discussion on group by in MySQL
  • How to use the query expression GroupBy in C#
  • Detailed explanation of the usage rules of group by in Oracle group query
  • How to use groupby in datatable for group statistics
  • Detailed explanation of group by usage

<<:  Install MySQL (including utf8) using Docker on Windows/Mac

>>:  Detailed explanation of Grid layout and Flex layout of display in CSS3

Recommend

27 Linux document editing commands worth collecting

Linux col command The Linux col command is used t...

Building a selenium distributed environment based on docker

1. Download the image docker pull selenium/hub do...

js native waterfall flow plug-in production

This article shares the specific code of the js n...

W3C Tutorial (5): W3C XML Activities

XML is designed to describe, store, transmit and ...

Detailed installation history of Ubuntu 20.04 LTS

This article records the creation of a USB boot d...

HTML table markup tutorial (48): CSS modified table

<br />Now let's take a look at how to cl...

Web page creation question: Image file path

This article is original by 123WORDPRESS.COM Ligh...

How to get form data in Vue

Table of contents need Get data and submit Templa...

Summary of MySQL basic common commands

Table of contents MySQL basic common commands 1. ...

Detailed explanation of setting up DNS server in Linux

1. DNS server concept Communication on the Intern...

How to avoid data loop conflicts when MySQL is configured with dual masters

I wonder if you have ever thought about this ques...

Solution to 700% CPU usage of Linux process that cannot be killed

Table of contents 1. Problem Discovery 2. View de...