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

Implementation of docker-compose deployment of zk+kafka+storm cluster

Cluster Deployment Overview 172.22.12.20 172.22.1...

Getting Started with Nginx Reverse Proxy

Table of contents Overview The role of reverse pr...

A brief discussion on the magic of parseInt() in JavaScript

cause The reason for writing this blog is that I ...

Analysis of Linux Zabbix custom monitoring and alarm implementation process

Target Display one of the data in the iostat comm...

Detailed explanation of JavaScript stack and copy

Table of contents 1. Definition of stack 2. JS st...

The latest Linux installation process of tomcat8

Download https://tomcat.apache.org/download-80.cg...

How to import Chinese data into csv in Navicat for SQLite

This article shares with you the specific method ...

How to dynamically add a volume to a running Docker container

Someone asked me before whether it is possible to...

Solution to Ubuntu cannot connect to the network

Effective solution for Ubuntu in virtual machine ...

How to write transparent CSS for images using filters

How to write transparent CSS for images using filt...

Detailed explanation of using Vue custom tree control

This article shares with you how to use the Vue c...

WeChat applet implements login interface

The login interface of WeChat applet is implement...

Web project development VUE mixing and inheritance principle

Table of contents Mixin Mixin Note (duplicate nam...

Detailed explanation of top command output in Linux

Preface I believe everyone has used the top comma...