Database SQL statements Learn notes (7)-Summarize data

Purpose: We often need to summarize data instead of actually retrieving them, such as determining the number of rows in a table, the sum of some columns, the maximum value of some columns, and so on. Retrieving all data at this time can only be a waste of time and system resources.

The first part: SQL gives 5 aggregate functions, which are functions that run on certain rows, and calculate and return a value.

SQL aggregation functions

Function	Description
AVG ()	Returns the average 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 a column's and

1.AVG ()

SELECT AVG As from the products;   

Output:

Note: (1) AVG () can only be used to determine the average value of a particular numeric column, and the alias must be given as a function parameter. To get the average of multiple columns, you must use more than one AVG () function.

(2) Ignore null values

2.COUNT () function

COUNT (*) counts the number of rows in a table, regardless of whether the table column contains null

Count (column) counts the number of rows in a particular column that are worth, ignoring null values

SELECT COUNT (* as from the products;

Results:

SELECT COUNT  as  from Customers;

Results:

3. MAX ()

SELECT MAX  as  from Products;

Note: When used with text data, MAX () returns the last row sorted by that column, ignoring rows with null column values

4.MIN ()

SELECT MIN  as  from Products;

Note that the section and the Max () function are the same

5. SUM ()

SELECT SUM  as  from WHERE order_num=20005;

Ignore rows with NULL column values

Part II

For the above 5 aggregation functions:

(1) Execute on all rows, specify the all parameter, or do not specify a parameter because all is the default behavior

(2) contains only a different value, specifying the DISTINCT parameter

SELECT AVG (DISTINCTas fromWHERE vend_id='DLL01';

Results: 4.240000

Part III

Combining aggregate functions

SELECT COUNT (*  as Num_items,MIN as min_price,MAX as Max_price  from Products;

Database SQL statements Learn notes (7)-Summarize data