Click on “SQL database development”, \
Set it as “top or star mark” to deliver dry goods in the first time
GROUP BY action The GROUP BY statement is used to GROUP result sets BY one or more columns in combination with aggregate functions.
GROUP BY grammar
SELECT column_name,
aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
Sample data table \
We take the table customers and orders as an example:
Customers table
List the orders
GROUP BY instance
Query how many customers live in each city. We can write SQL like this:
SELECT city,COUNT(*) AS number of Customers FROM Customers GROUP BY cityCopy the codeThe result is: \
As you can see from above, GROUP BY aggregates customers with the same city name and counts them using the COUNT function.
Before using the GROUP BY clause specifically, there are some important rules to know.
- The GROUP BY clause can contain any number of columns. This enables grouping to be nested, providing finer control over data grouping.
- Each column listed in the GROUP BY clause must be a retrieval column or a valid expression (but not an aggregation function).
- Every column in a SELECT statement must be given in the GROUP BY clause, except for the aggregate calculation statement.
- If there is a NULL value in the grouping column, NULL is returned as a grouping. If there are multiple rows of NULL values in a column, they are grouped.
- The GROUP BY clause must appear after the WHERE clause and before the ORDER BY clause.
In the example above, we can find that the usage of GROUP BY satisfies the above requirement:
In addition to the aggregate function COUNT, cities are listed in the SELECT statement and the GROUP BY clause.
GROUP BY Join multiple tables
Query how many orders are generated for each city. We can write SQL like this:
SELECT * FROM Customers c LEFT JOIN Orders o ON C SELECT * FROM Customers C LEFT JOIN Orders o ON C Customer ID= O. Customer ID GROUP BY C. cityCopy the codeThe result is: \
Since there are no users in Beijing and Hangzhou to purchase products, the order quantity is 0
GROUP BY weight
GROUP BY automatically excludes duplicate data when grouping.
Group cities and provinces without any aggregation operations:
SELECT customers GROUP BY city, province FROM customersCopy the codeThe result is: \
This function is the same as the DISTINCT keyword function in SQL, but has different performance. This function is not extended here. \
-- End -- background reply keyword:1024, to obtain a carefully organized technical dry goods background reply keywords: into the group, take you into the master like clouds of communication group. Recommended reading ali regulation over3Select * from table where JOIN is prohibited. MySQL common commands5After we parachuted in an expert, half of the programmers went crazy... A free than Navicat easy to use, powerful database management tools this is a can learn technology public number, welcome attentionCopy the code