group by means "group query", which can be understood as "grouping (Group) according to (by) certain rules"; its function is to divide a data set into several small ones through certain rules area, and then perform data processing on several small areas. In MySQL, GROUP BY is an optional clause of the SELECT statement that is used to group query results based on one or more fields. It reduces the number of rows in the result set.
The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.
group by means "group query".
The Group By statement literally means "group (Group) according to (by) certain rules" in English. Its function is to divide a data set into several small areas through certain rules, and then perform data processing on several small areas.
MySQL uses GROUP BY group query
In MySQL, the GROUP BY keyword can group query results based on one or more fields.
The GROUP BY clause groups a set of rows into a small group of summary row records by the value of a column or expression. The GROUP BY clause returns one row for each group. In other words, it reduces the number of rows in the result set.
The GROUP BY clause is often used with aggregate functions such as SUM, AVG, MAX, MIN and COUNT. Aggregate functions are used in the SELECT clause to calculate information about each grouping.
The GROUP BY clause is an optional clause of the SELECT statement. The following is the GROUP BY clause syntax:
SELECT c1, c2,..., cn, aggregate_function(ci) FROM table WHERE where_conditions GROUP BY c1 , c2,...,cn;
The GROUP BY clause must appear after the FROM and WHERE clauses. Following the GROUP BY keyword is a comma-separated list of columns or expressions that are to be used as conditions to group rows.
MySQL GROUP BY example
1. Simple MySQL GROUP BY example
Let’s take a look at the sample database (yiibaidb ), its structure is as follows -
mysql> desc orders;
Assuming that you want to group the values of the order status into subgroups, you need to use the GROUP BY clause and specify Perform grouping by status column, query as follows:
SELECT status FROM orders GROUP BY status;
Execute the above query statement and get the following results-
You can see that the GROUP BY clause returns The status value is unique. It works like the DISTINCT operator as shown in the following query:
SELECT DISTINCT status FROM orders;
Execute the above query statement and get the following results-
2 , MySQL GROUP BY and aggregate functions
You can use aggregate functions to perform calculations on a group of rows and return a single value. The GROUP BY clause is often used with aggregate functions to perform calculations for each group and return a single value.
For example, if you want to know the number of orders in each status, you can use the COUNT function and the GROUP BY clause query statement, as shown below:
SELECT status, COUNT(*) AS total_number FROM orders GROUP BY status;
Execute the above query statement and get the following results -
Please refer to the following orders (orders) and order details (orderdetails) tables, their ER diagram is as follows-
To get the total amount of all orders by status, you can use the orderdetails table to connect the orders table, and use the SUM function to calculate the total amount. Please refer to the following query:
SELECT status, SUM(quantityOrdered * priceEach) AS amount FROM orders INNER JOIN orderdetails USING (orderNumber) GROUP BY status;
Executing the above query, you get the following results -
Similarly, the following query returns the order number and the total amount of each order .
SELECT orderNumber, SUM(quantityOrdered * priceEach) AS total FROM orderdetails GROUP BY orderNumber;
Execute the above query and get the following results-
3. MySQL GROUP BY expression example
In addition to columns, rows can be grouped by expressions. The following query gets the total sales per year.
SELECT YEAR(orderDate) AS year, SUM(quantityOrdered * priceEach) AS total FROM orders INNER JOIN orderdetails USING (orderNumber) WHERE status = 'Shipped' GROUP BY YEAR(orderDate);
Execute the above query and get the following results -
In this example, we use the YEAR function to extract year data from the order date (orderDate). Only orders with Shipped status are included. Note that the expressions appearing in the SELECT clause must be the same as those in the GROUP BY clause.
MySQL GROUP BY and HAVING clause
You can use the HAVING clause to filter the groups returned by the GROUP BY clause. The following query uses the HAVING clause to select annual sales totals from 2013 onwards.
SELECT YEAR(orderDate) AS year, SUM(quantityOrdered * priceEach) AS total FROM orders INNER JOIN orderdetails USING (orderNumber) WHERE status = 'Shipped' GROUP BY year HAVING year > 2013;
Execute the above query and get the following results -
GROUP BY子句:MySQL与标准SQL
标准SQL不允许使用GROUP BY子句中的别名,但MySQL支持此选项。以下查询从订单日期提取年份,并对每年的订单进行计数。该year用作表达式YEAR(orderDate)的别名,它也用作GROUP BY子句中的别名,此查询在标准SQL中无效。
参考以下查询 -
SELECT YEAR(orderDate) AS year, COUNT(orderNumber) FROM orders GROUP BY year;
执行上面查询,得到以下结果 -
MySQL还允许您以升序或降序(标准SQL不能提供)对组进行排序。默认顺序是升序。例如,如果要按状态获取订单数量并按降序对状态进行排序,则可以使用带有DESC的GROUP BY子句,如下查询语句:
SELECT status, COUNT(*) FROM orders GROUP BY status DESC;
执行上面查询,得到以下结果 -
请注意,在GROUP BY子句中使用DESC以降序对状态进行排序。我们还可以在GROUP BY子句中明确指定ASC,按状态对分组进行升序排序。
【相关推荐:mysql视频教程】
The above is the detailed content of What does group by mean?. For more information, please follow other related articles on the PHP Chinese website!

MySQL'sBLOBissuitableforstoringbinarydatawithinarelationaldatabase,whileNoSQLoptionslikeMongoDB,Redis,andCassandraofferflexible,scalablesolutionsforunstructureddata.BLOBissimplerbutcanslowdownperformancewithlargedata;NoSQLprovidesbetterscalabilityand

ToaddauserinMySQL,use:CREATEUSER'username'@'host'IDENTIFIEDBY'password';Here'showtodoitsecurely:1)Choosethehostcarefullytocontrolaccess.2)SetresourcelimitswithoptionslikeMAX_QUERIES_PER_HOUR.3)Usestrong,uniquepasswords.4)EnforceSSL/TLSconnectionswith

ToavoidcommonmistakeswithstringdatatypesinMySQL,understandstringtypenuances,choosetherighttype,andmanageencodingandcollationsettingseffectively.1)UseCHARforfixed-lengthstrings,VARCHARforvariable-length,andTEXT/BLOBforlargerdata.2)Setcorrectcharacters

MySQloffersechar, Varchar, text, Anddenumforstringdata.usecharforfixed-Lengthstrings, VarcharerForvariable-Length, text forlarger text, AndenumforenforcingdataAntegritywithaetofvalues.

Optimizing MySQLBLOB requests can be done through the following strategies: 1. Reduce the frequency of BLOB query, use independent requests or delay loading; 2. Select the appropriate BLOB type (such as TINYBLOB); 3. Separate the BLOB data into separate tables; 4. Compress the BLOB data at the application layer; 5. Index the BLOB metadata. These methods can effectively improve performance by combining monitoring, caching and data sharding in actual applications.

Mastering the method of adding MySQL users is crucial for database administrators and developers because it ensures the security and access control of the database. 1) Create a new user using the CREATEUSER command, 2) Assign permissions through the GRANT command, 3) Use FLUSHPRIVILEGES to ensure permissions take effect, 4) Regularly audit and clean user accounts to maintain performance and security.

ChooseCHARforfixed-lengthdata,VARCHARforvariable-lengthdata,andTEXTforlargetextfields.1)CHARisefficientforconsistent-lengthdatalikecodes.2)VARCHARsuitsvariable-lengthdatalikenames,balancingflexibilityandperformance.3)TEXTisidealforlargetextslikeartic

Best practices for handling string data types and indexes in MySQL include: 1) Selecting the appropriate string type, such as CHAR for fixed length, VARCHAR for variable length, and TEXT for large text; 2) Be cautious in indexing, avoid over-indexing, and create indexes for common queries; 3) Use prefix indexes and full-text indexes to optimize long string searches; 4) Regularly monitor and optimize indexes to keep indexes small and efficient. Through these methods, we can balance read and write performance and improve database efficiency.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

SublimeText3 Linux new version
SublimeText3 Linux latest version

Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

VSCode Windows 64-bit Download
A free and powerful IDE editor launched by Microsoft

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool

MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.
