MySQL Learning record-grouping and aggregation

First, GROUP BY clause

Group by words can group data.

Example of a Film data table in MySQL5.5 's Sakila database: Find out the total number of movies for each movie level

MySQL>SELECTCOUNT(* as from film     >  GROUP by

The results are as follows:

As you can see, the GROUP BY clause groups The fields that follow, and it also uses the aggregate function count () to count the number of items in each group.

Two, aggregation function

As can be known from the above example, the aggregation function performs a specific action on all rows of a group. Here are some common aggregation functions:

Max (): Returns the maximum value in the collection

MIN (): Returns the minimum value in the collection

AVG (): Returns the average in the collection

SUM (): Returns the sum of all values of the collection

COUNT (): Returns the total number of bars in the collection

Or an example of a film data table: Note: The Length field represents the movie duration in the datasheet

MySQL>Selectmax(length),    min(length),      - avg (length),      - sum (length),      - Count (*)      -  from film;

Results The following

The aggregation function creates a parameter expression that can be arbitrarily increased in complexity as needed, with the assurance that a number, string, or date is returned at the end.

Third, the processing of the aggregation function to null value

　　The sum (), AVG (), MAX () functions ignore null values in the grouped collection. It is important to note that count (field) is the number of statistics in the grouped collection, ignores null, and COUNT (*) represents the number of rows in the statistics grouping collection and does not omit null.

Iv. HAVING clause

Look at the example: Find out the total number of movies in each movie level and the total is greater than 200

MySQL>SELECTCOUNT(* as from film      GROUP by rating    ,WHERE ratingcount>  ;

Error 1064 (42000): You have a error in your SQL syntax; Check the manual that --Error
Corresponds to your MySQL server version for the right syntax to use near ' WHERE
Ratingcount>200 ' at line 3

　　The WHERE clause of a query cannot contain aggregate functions because the WHERE clause is executed before grouping, and the server cannot perform any functions on the grouping at this time . Therefore, you can use the HAVING clause to filter the data using the aggregation function

mysql> Select rating, count(*)  as Ratingcount  from Film   , group  by rating    , have RATINGC Ount>; +--------+-------------+|Rating|Ratingcount|+--------+-------------+|Pg- -  |         223 ||NC- -  |          About |+--------+-------------+2Rowsinch Set(0.00Sec

V. WHERE and having in a group query

When adding filters to a query that contains a GROUP BY clause, you need to consider whether the filter is for the original data (where the filter should be placed in the WHERE clause) or for the grouped data (where the filter condition should be placed in the HAVING clause).

MySQL Learning record-grouping and aggregation