0007 notes 03-Summary and grouping data in SQL, SQL: Yes 03-

0007 notes 03-Summary and grouping data in SQL, SQL: Yes 03-

1. In some cases, the summary value of the data is required, rather than the data itself. For example, for some data, the sum, count, the maximum and minimum values, and the average value, there are five Aggregate functions: AVE (), COUNT (), MAX (), MIN (), SUM ():

(1) calculate the average value: AVE (). An AVG () is only valid for one field. Note that AVE () ignores the NULL value instead of using it as "0" for calculation:

Select avg (field name 1), ''', AVG (field name n) FROM table name WHERE statement;

　　　　

(2) COUNT: COUNT (), two usage: COUNT (*): COUNT the number of rows in the table, whether or not NULL; COUNT (field name ): counts rows with data in a specific column and ignores NULL values.

(3) maximum and minimum values: MAX () and MIN (): used mostly for values and dates. The text can also be used. The maximum or minimum values in ascending order. All ignore NULL

(4) SUM: SUM (): You can SUM a single column or the product of multiple columns:

(5) ALL and DISTINCT: it can be used for the above five functions. The default value is ALL. The calculation for non-repeated values is DISTINCT. It is used in function parameters and is separated from field names by spaces.

(6) combination of Aggregate functions: the preceding functions can be included in SELECT statements, such as the total number of Products in Products, average price, maximum price, and lowest price.

2. Logically grouping data: for example, finding the total number of commodities, average price, maximum price, and lowest price of each supplier

　　

(1) Rules for using group:

1. The group by statement must appear after WHERE, before ORDER

2. If NULL exists in the group column, this will also be a group

3. Except for the aggregate calculation statement, the SELECT column in the SELECT statement must appear in group.

4. The group by column must be a query column or expression. If the expression is used in SELECT, the same expression must be used in group by, and aliases cannot be used.

5. group by can contain any number of columns and can be nested.

3. filter groups: HAVING is used in combination with group by. Similar to WHERE, HAVING only applies to rows and logical groups. For example, HAVING lists two or more products, suppliers whose prices are greater than or equal to 4:

Rows filtered out by where are not grouped by group.

4. group by and order by: The data output by group by is not necessarily ordered. order by can use any column (including non-selected columns ), however, group by can only be used and must use a Select column fire expression column. order by can not be used, but group by must be used when a Select column (or expression column) is used together with the aggregate function.

5. SELECT, FROM, WHERE, group by, HAVIN, and GORDER