Shulou(Shulou.com)06/02 Report--

This article mainly introduces "how to master SQL aggregation, grouping and sorting". In daily operation, I believe many people have doubts about how to master SQL aggregation, grouping and sorting. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "how to master SQL aggregation, grouping and sorting". Next, please follow the editor to study!

I. aggregate query

When accessing a database, it is often necessary to make a statistical summary of a column of data in a table, such as summation, maximum, minimum, average, etc., so you need to use an aggregate function, which is a function used for summarization. Aggregation is the summary of multiple rows into one row.

Common aggregate functions are as follows:

1. COUNT function

The count function is used to record the number of rows in a statistical table.

For example, calculate the number of rows for all data:

SELECT COUNT (*) FROM users

Note: COUNT (*) will get the number of data rows containing null NULL. If you want to exclude data rows containing NULL, you can use count (field name) to get the number of data rows other than NULL.

SELECT COUNT (user_name) FROM users;2, SUM functions

Used to calculate the sum of data in any column.

For example, calculate the sum of the ages of all users:

SELECT sum (age) FROM users;3, AVG functions

Used to calculate the average of the data in any column.

For example, calculate the average age of all users:

SELECT AVG (age) FROM users;4, MAX function and MIN function

The MAX function is used to calculate the maximum value of data in any column, and the MIN function is used to calculate the minimum value of data in any column.

For example, calculate the maximum and minimum ages of all users:

SELECT MAX (age), MIN (age) FROM users

Note: the MAX and MIN functions apply to columns of almost all data types, while the SUM and AVG functions apply only to columns of numeric types.

2. Grouping query

The aggregate function is a statistical summary of all the data in the table, and you can also use the GROUP BY clause to divide the data into several groups, and then make a statistical summary.

Syntax format:

SELECT,... FROM GROUP BY,...

For example, the sum of users in each city is grouped by the city where the user is located:

SELECT city,count (*) FROM users GROUP BY city;+-+-+ | city | count (*) | +-+-+ | Beijing | 60 | | Shanghai | 45 | NULL | 80 | | Jinan | 12 | +-+-+

As you can see from the results, a NULL field is also listed as a grouping. If you want to exclude, you can use the where clause.

SELECT city,count (*) FROM users WHERE city IS NOT NULL GROUP BY city; 3. Filter the aggregation results

When we use the GROUP BY clause to group groups, sometimes we need to filter the aggregation results of the groups. we may first think of using the WHERE clause, in fact, it is not, but with the HAVING clause. HAVING plays the same role as WHERE, except that WHERE is used to filter data rows, while HAVING is used to filter the results of packet aggregation.

For example, group by user's city, and filter groups with more than 40 users in the group:

SELECT city,COUNT (*) AS num FROM users GROUP BY city HAVING num > 40

Another example: grouping according to the city of the user, and filtering the group in which the average age of the user is less than 25.

SELECT city,AVG (age) AS avg_age FROM users GROUP BY city HAVING avg_age

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.