0007 ' SQL must know ' Note 03-Summarize and group data

1, sometimes need the aggregate value of the data, rather than the data itself, such as summing up some data, counting, the maximum minimum value, averaging, so there are 5 aggregation functions: AVE (), count (), MAX (), MIN (), SUM ():

(1) Averaging: Ave (), an AVG () is valid only for one field, Note that AVE () ignores null values instead of participating in the calculation as "0" :

SELECT AVG (field name 1), "',AVGfromWHERE clause;

　　　　

(2) Count: Count (), two usages: count (*): Counts the number of rows in a table, regardless of whether there is a null;count (field name): Counts the rows with data for a particular column, ignoring null values

(3) Maximum, Minimum: Max (), Min (): More for numeric and date, text can also be used, the maximum or minimum value in ascending order. Both ignore null

(4) Sum: Sum (): You can sum a single column or multiply a number of columns:

(5) All and distinct: can be used for the above 5 functions, the default is all, the calculation of non-repeating values is distinct, used in the parameters of the function, with the field name separated by a space

(6) Combination of aggregation functions: the above functions can be included in the SELECT statement, such as the total number of products in the product, the average price, the highest price, the lowest price

2. Logically group data: for example, the total number of products for each supplier, the average price, the highest price, the lowest price

　　

(1) The terms of use of GROUP by:

The 1.GROUP by statement must appear after the where, before order by

2. If there is null in the Grouping column, this will also be used as a set of

3. In addition to the clustered calculation statement, select columns in select must appear in GROUP by

The column for 4.GROUP by must be a column or an expression, and if an expression is used in Select, the same expression will be used in GROUP by, and the alias

5.GROUP by can contain any number of columns that can be nested

3. Filter groups: Having, in conjunction with group by, is similar to where, just where for rows, having a logical grouping, such as a list of 2 or more products, and whose price is greater than or equal to 4 of the suppliers:

Where filtered rows do not participate in GROUP by grouping

4. Group BY and sort (order by): The data for group by output is not necessarily sequential; order by can use any column (including non-select columns), but group by only and must use the Select column Fire expression column; ORDER by can not, but group By when you have a selection column (or an expression column) with the aggregation function, you must use it.

5. SELECT, from, WHERE, GROUP by, Havin, gorder by order

0007 ' SQL must know ' Note 03-Summarize and group data