Detailed explanation of MySQL data grouping

Detailed explanation of MySQL data grouping

Create Group

Grouping is established in the GROUP BY clause of the SELECT statement.

example:

SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;

GROUP BY

  • The GROUP BY clause can contain any number of columns, which allows nested groupings and provides more detailed control over data grouping.
  • If you nest groups in the GROUP BY clause, the data will be aggregated at the last specified grouping. In other words, when creating a grouping, all specified columns are calculated together. (So ​​data cannot be retrieved from individual columns).
  • Each column listed in the GROUP BY clause must be a search column or a valid expression (but not an aggregate function). If you use an expression in the SELECT clause, you must specify the same expression in the GROUP BY clause. You cannot use an alias.
  • Except for aggregate calculation statements, every column in the SELECT statement must be given in the GROUP BY clause.
  • If there is a NULL value in the grouping column, NULL will be returned as a group. If there are multiple rows with NULL values ​​in the column, they will be grouped together.
  • The GROUP BY clause must appear after the WHERE clause and before the ORDER BY clause.

Filter Groups

If one wants to list all orthopedics departments that have at least two orders, the data must be filtered based on the complete grouping rather than individual rows.

You can use HAVING

SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;

The difference between HAVING and WHERE is that WHERE filters before data grouping, while HAVING filters after grouping. Of course, both can appear in the same statement.

List suppliers with 2 or more products with a price of 10 or more

SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >= 2;

List suppliers with two top products

SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*) >= 2;

Grouping and Sorting

ORDER BY GROUP BY
Sorting the output Group rows. But the output may not be in the order of the groups
Any column can be used (even non-selected columns) Only select columns or expression columns may be used, and each select column expression must be used.
Not necessarily required If you use a column (expression) with an aggregate function, you must use

Retrieve the order number and total order price of orders with a total order price greater than or equal to 50

SELECT order_num, SUM(quantity*item) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >= 50;

If you sort the output by total order price

SELECT order_num, SUM(quantity * item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity * item_price) >= 50 ORDER BY ordertotal;

Order of SELECT clauses

Clauses illustrate Is it necessary to use
SELECT The column or expression to return yes
FROM The table from which to retrieve data Only used when selecting data from a table
GROUP BY Group Description Only used when computing aggregates by group
WHERE Row-level filtering no
HAVING Group level filtering no
ORDER BY Output sort order no
LIMIT The number of rows to retrieve no

The above is a detailed explanation of MySQL data grouping. For more information about MySQL data grouping, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • MySql grouping and randomly getting one piece of data from each group
  • Implement group by based on MySQL to get the latest data of each group
  • MySQL subqueries and grouped queries
  • MySQL grouping queries and aggregate functions
  • MySql Group By implements grouping of multiple fields
  • Detailed explanation of MySQL group sorting to find the top N
  • Detailed example of getting the maximum value of each group after grouping in MySQL
  • Mysql uses group by group sorting

<<:  Analysis of the usage of Xmeter API interface testing tool

>>:  Markup Language - Image Replacement

Recommend

Echart Bar double column chart style most complete detailed explanation

Table of contents Preface Installation and Config...

Some experience sharing on enabling HTTPS

As the domestic network environment continues to ...

Analysis of the causes of accidents caused by Unicode signature BOM

Maybe you are using include files here, which is u...

MySQL 8.0.24 installation and configuration method graphic tutorial

This article shares the installation tutorial of ...

The docker-maven-plugin plugin cannot pull the corresponding jar package

When using the docker-maven-plugin plug-in, Maven...

MySQL 8.0.20 compressed version installation tutorial with pictures and text

1. MySQL download address; http://ftp.ntu.edu.tw/...

A detailed introduction to seata docker high availability deployment

Version 1.4.2 Official Documentation dockerhub st...

How to define input type=file style

Why beautify the file control? Just imagine that a...

js to realize simple shopping cart function

This article example shares the specific code of ...

5 Reasons Why Responsive Web Design Isn’t Worth It

This article is from Tom Ewer's Managewp blog,...

How to solve the mysql insert garbled problem

Problem description: When inserting Chinese chara...

MySQL 8.0.16 installation and configuration graphic tutorial under macOS

This article shares the installation and configur...

Detailed explanation of the use of redux in native WeChat applet development

premise In complex scenarios, a lot of data needs...

js implements a simple shopping cart module

This article example shares the specific code of ...

Why is IE6 used by the most people?

First and foremost, I am a web designer. To be mor...