# MySQL knowledge learning-the twelfth day: summary data

# Preface

It's been twelve days without knowing it. We already have a preliminary understanding of MySQL and have mastered some basic usage. The exercises on Niuke.com can basically be solved, but our next goal is how to solve better.

# Aggregate function

We often need to summarize data without actually retrieving them. For this purpose, MySQL

provides special functions.

Aggregate functions (aggregate function) running in the row group, and the calculationreturns a single valuefunction

## AVG function

AVG() finds the average value of the column by counting the number of rows in the table and calculating the sum of the values of a specific column.

AVG() can be used to return the average value of all columns, or it can be used to return the average value of a specific column or row.

Consider the following table (product)

The above is to return the average value in the number column. Similarly, the AVG function can be used with the where clause to calculate the average value of a specific column value. The

above means that only the average value of a column can be calculated (the data type of the column must be a numeric value, otherwise the returned result is wrong)

We always add a record to the table product.

Before running the code,

we find that the average value is (27+34+4)/3=21.6667, ignoring the fourth row.

## Count function

The COUNT() function counts. You can use COUNT() to determine the number of rows in the table or the number of rows that meet specific conditions.

When count counts, you can consider NULL values or not, depending on your usage.

The number of rows in the statistics table (that is, the number of records, regardless of whether the record uses a null value, it will be recorded)

The following is the statistics column, if the column value is null, it will not be counted

## MAX function

MAX() returns the maximum value in the specified column. MAX() requires the name of the column to be specified.

max can **only count** the maximum value of **a column** .

## MIN function

The function of MIN() is just the opposite of the function of MAX(), which returns the minimum value of the specified column. Like MAX(), MIN() requires the column name to be specified.

The MAX and MIN functions can also be used in conjunction with the WHERE clause to return the maximum and minimum values of the specified column.

## SUM function

**SUM() is used to return the sum (total) of the specified column value.**

# Aggregate different values

Look at the following example

The above average value is (27+34+4)/3=21.6667, because 27 appears twice, the DISTINCT keyword tells the AVG function to only count different values, so 27 only counts once.

# Combined aggregate function

We can use multiple aggregate functions

**at the** same time. **When using multiple aggregate functions, the last aggregate function cannot be followed by a comma. As**

mentioned above, aliasing can be implemented by **yourself.** Please pay attention **to keep the column names in the table as unique as possible.**

The happy weekend is almost over, sad~