MySQL data aggregation and grouping

We often need to summarize data without actually retrieving them, and MySQL provides special functions for this purpose. Using these functions, MySQL queries can be used to retrieve data for analysis and report generation.

Examples of this type of search include:

• Determines the number of rows in a table (or the number of rows that meet a condition or contain a specific value).
• Gets the sum of groups of rows in a table.
• Find the maximum, minimum, and average values ​​of a table column (or all rows or some specific rows)

like:

AVG() returns the average value of a column COUNT() returns the number of rows in a column MAX() returns the maximum value of a column MIN() returns the minimum value of a column SUM() returns the sum of the values ​​in a column

For example:

select AVG(prod_price) as avg_price
from products

For example:

Here, distinct can be set in some functions, such as avg . If it is set, there will be a phenomenon like this. It only counts the average values ​​between different values, depending on the specific business needs.

The following describes the grouped data:

Returns the number of products offered by each supplier:

select COUNT(*) as num_prods,vend_id
from products
GROUP BY vend_id

Things to note when using group by :

• GROUP BY clause can contain any number of columns. This allows groups to be nested, providing more granular control over data grouping.
• If you nest groupings in GROUP BY子, the data will be aggregated on the last specified grouping. In other words, when creating a group, all the specified columns are calculated together (so you cannot retrieve data from individual columns)
• Each column listed in GROUP BY clause must be a search column or a valid expression (but not an aggregate function). If you use an expression in SELECT , you must specify the same expression in GROUP BY clause. Aliases cannot be used.
• Except for aggregate calculation statements, every column in the SELECT statement must be given in GROUP BY clause.
• If there is a NULL value in the grouping column, NULL will be returned as a group. If there are multiple rows with NULL values ​​in the column, they will be grouped together.
• GROUP BY clause must appear after the WHERE clause and before the ORDER BY clause.

Using ROLLUP Using the WITH ROLLUP keyword, you can get the value of each group and each group summary level (for each group).

As shown below:

select COUNT(*) as num_prods,vend_id
from products
GROUP BY vend_id with ROLLUP

In addition to grouping data using GROUP BY , MySQL also allows filtering groups to specify which groups to include and which groups to exclude. For example, you might want to list all customers who have at least two orders. To get this kind of data, you have to filter based on complete groups rather than individual rows.

Here is another way to understand the difference between HAVING and WHERE . WHERE filters before data is grouped, HAVING filters after data is grouped. This is an important distinction; rows excluded by the WHERE statement are not included in the grouping. This may change the calculated values ​​and thus affect groups that are filtered out in the HAVING clause based on those values.

select COUNT(*) as num_prods,vend_id
from products
GROUP BY vend_id
HAVING COUNT(*)>2

So, is there a need to use both WHERE and HAVING clauses in one statement? In fact, there is. Suppose you want to further filter the above statement to return customers who have placed more than two orders in the past 12 months. To achieve this, you can add a WHERE clause to filter out orders that were placed within the past 12 months. Then add the HAVING clause to filter out groups with more than two orders.

select COUNT(*) as num_prods,vend_id
from products
where prod_price>=10
GROUP BY vend_id

Here is an introduction to order by :

Although GROUP BY and ORDER BY often accomplish the same job, they are very different.

Why is it said here group by and order by complete the same work? This is because, as we discovered earlier, the data actually appears to be sorted by vend_id.

We often find that data grouped by GROUP BY is indeed output in the order of the groups. But this is not always the case, and it is not required by the SQL specification. Additionally, users may request to sort in an order other than the order in which they were grouped. Just because you group your data in a certain way (to get a specific grouping aggregate value), it doesn't mean you need to order the output in the same way.

An explicit ORDER BY clause should be provided even if its effect is equivalent to GROUP BY clause

Don't forget ORDER BY Generally, when using GROUP BY clause, you should also give ORDER BY clause. This is the only way to guarantee that the data is sorted correctly. Never rely solely on GROUP BY to sort data.

For example:

select COUNT(*) as num_prods,vend_id
from products
where prod_price>=10
GROUP BY vend_id
ORDER BY num_prods

Select statement sequence:

SELECT Columns or expressions to return Yes FROM Table from which to retrieve data WHERE Row-level filtering No GROUP BY Grouping specification Used only when computing aggregates by group HAVING Group-level filtering No ORDER BY Output sort order No LIMIT Number of rows to retrieve No

This is the end of this article about mysql data aggregation and grouping. For more relevant mysql grouping 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!