mysql data grouping: create a group

mysql data grouping

SQL aggregate functions can be used to summarize data. This allows us to count rows, calculate sums and averages, and get maximum and minimum values ​​without retrieving all the data.

All calculations so far have been performed on all data in the table or data matching a specific where clause. As a reminder, the following example returns the number of products provided by supplier 1003:

Input:

select count(*) as mun_prods from products where vend_id = 1003;

Output:

But what if you want to return the number of products provided by each supplier? Or what if we return products provided by suppliers who only provide a single product, or return suppliers who provide more than 10 products?

This is the time for groups to show their skills. Grouping allows data to be divided into multiple logical groups so that aggregate calculations can be performed on each group.

mysql creates a group

Group is established in the group by clause of the select statement. The best way to understand grouping is to look at the following example:

Input:

select vend_id,count(*) as num_prods from products group by vend_id;

Output:

Analysis: The above select statement specifies two columns, vend_id contains the ID of the product supplier, and num_prods is the calculated field (Created using the count(*) function). Group by clause knowledge MySQL sorts and groups data by vend_id. This causes num_prods to be calculated once for each vend_id instead of the entire table. As you can see from the output, supplier 1001 has 3 products, supplier 1002 has 2 products, supplier 1003 has 7 products, and supplier 1005 has 2 products.

Because group by is used, there is no need to specify each group to be calculated and valued. The system will do it automatically. The group by clause prompts MySQL to group the data and then aggregate each group instead of the entire result set.

Before using the group by clause, you need to know some important regulations:

1. The group by clause can contain any number of columns. This enables nesting of groups, providing more granular control over grouping of data.

2. If grouping is nested in the group by clause, the data will be summarized on the last specified grouping. In other words, when building a group, all columns specified are calculated together (so data cannot be retrieved from individual columns).

3. Each column listed in the group by clause must be a retrieval column or a valid expression (but not an aggregate function). If you use an expression in select, you must specify the same expression in the group by clause. Aliases cannot be used.

4. Except for aggregate calculation statements, each column in the select statement must be given in the group by clause.

5. If there is a NULL value in the grouping column, NULL will be returned as a grouping. If there are multiple rows of NULL values ​​in a column, they will be grouped together.

6. The group by clause must appear after the where clause and before the order by clause.

【Related recommendations】

1. mysql data grouping: filter grouping

2. mysql data grouping and sorting and SELECT clause order
   

The above is the detailed content of mysql data grouping: create a group. For more information, please follow other related articles on the PHP Chinese website!