SQL Aggregation, Grouping, and Sorting

1. Aggregate Query

When accessing a database, you often need to perform statistical summary on a column of data in a table, such as sum, maximum value, minimum value, average value, etc. At this time, you need to use an aggregate function. The so-called aggregate function is a function used for summary. Aggregation is to aggregate multiple rows into one row.

Common aggregation functions are as follows:

1. COUNT function

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

For example, to count the number of rows in all data:

SELECT COUNT(*) FROM users;

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

SELECT COUNT(user_name) FROM users;

2. SUM function

Used to calculate the sum of data in any column.

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

SELECT sum(age) FROM users;

3. AVG function

Used to calculate the average of data in any column.

For example, to 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, to calculate the maximum and minimum ages among all users:

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

Note: The MAX function and MIN function are applicable to columns of almost all data types, while the SUM function and AVG function are only applicable to columns of numeric types.

2. Group Query

Aggregate functions are used to summarize statistics for all data in a table. You can also use GROUP BY clause to divide the data into several groups and then perform statistical summaries.

Syntax format:

SELECT <field name>,... FROM <table name> GROUP BY <field name>,...;

For example, group users by their city and count the sum of users in each city:

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

From the results, we can see that fields with NULL value will also be listed as a group. 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 GROUP BY clause to group, sometimes we need to filter the grouped aggregate results. We may first think of using the WHERE clause, but in fact, it is not the case. Instead, we use the HAVING clause. The role of HAVING is the same as that of WHERE , both of which are filtering, but WHERE is used to filter data rows, while HAVING is used to filter grouped aggregate results.

For example, group users by their city and filter groups with more than 40 users:

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

Another example: group users by their city, and filter out groups where the average age of users is less than 25.

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

1. Elements of the HAVING clause

There are three types of elements that can be used in the HAVING clause:

• constant
• Aggregate functions
• The column name specified in GROUP BY clause (i.e., the aggregate key)

4. Sort the query results

Sorting can be used in SQL queries to sort data in ascending ( ASC ) or descending ( DESC ) order. The default is ascending order.

Syntax format:

SELECT <field name>,... FROM <table name> ORDER BY <field name> ASC/DESC,...;

For example, to sort the records in the users table in ascending order by age:

SELECT * FROM users ORDER BY age ASC;

Note: ASC can be omitted for ascending order, but DESC is required for descending order.

For example, to sort the records in the users table in descending order by age:

SELECT * FROM users ORDER BY age DESC;

1. Specify multiple sort keys

Multiple sort keys can be specified in ORDER BY clause. For example, to sort the records in the users table in descending order of age and ascending order of registration time:

SELECT * FROM student ORDER BY age DESC,register_time ASC;

When sorting multiple fields, separate them with “,”.

2. Sorting using aggregate functions

ORDER BY clause can also use the results of aggregate functions for sorting.

For example, group users by their city and sort by the number of users in each group:

SELECT city,COUNT(*) AS num FROM users GROUP BY city ORDER BY num;

This is the end of this article about SQL aggregation, grouping and sorting. For more relevant SQL aggregation, grouping and sorting content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!