05_mysql Common Functions _ Grouping functions

# Grouping functions
/*
Functions: Statistics, also known as aggregate functions, statistical functions, group functions
Pass in a set of values and get a value after statistics

Classification:
Sum sum, avg average, max Max, Min min, count calculation

Characteristics:
1. SUM,AVG Processing Numerical type
Max,min,count Handling any type

2. Whether to ignore null values
The grouping function ignores null values and does not participate in statistics

3. Can be paired with distinct, first to re-calculate

4. Detailed description of the Count function
Count (x) x field's non-null lines
Total number of rows in the count (*) Table (if there are 1 fields in a row that are not NULL, count in)
Total number of rows in the count (constant) table (equivalent to 1 columns in the table, with a value of a given constant

Statistical efficiency:
The storage engine is MyISAM, COUNT (*) is the best, and there are 1 internal counters that save count (*) directly
The storage engine is similar to InnoDB, COUNT (*) and COUNT (1), but is much more efficient than count (' constant field ') (To determine null)

5, and the Grouping function query fields are limited (return logical table, structure to rule)
Request is a field after group by (logical table to rule)


*/

# 1, Simple to use
SELECT SUM (salary) from employees;
SELECT AVG (salary) from employees;
SELECT MAX (salary) from employees;
SELECT MIN (salary) from employees;
SELECT COUNT (salary) from employees;

SELECT SUM (Salary) as and, TRUNCATE (avg (Salary), 4) average, max (salary) max
From employees;

# 2. What types of parameters are supported?
# sum,avg: Numeric type only makes sense
# max,min: Numeric type, character type (dictionary order), date type
# count: Numeric, character, date type (number of non-null)
SELECT MAX (HireDate), MIN (HireDate) from employees;
SELECT count (last_name), COUNT (commission_pct) from employees; # 107,35

# 3, whether to ignore null values

# Ignore Null values
SELECT SUM (commission_pct), AVG (commission_pct) from employees;

# Ignore Null values
SELECT MAX (commission_pct), MIN (commission_pct) from employees;

# 4, with distinct (go to weight statistics)
SELECT sum (DISTINCT salary), sum (salary) from employees; # go ahead and make the sum

SELECT count (DISTINCT salary), count (salary) from employees; # Go ahead and re-count #

SELECT MAX (DISTINCT salary), MIN (DISTINCT salary) from employees; # First go to the heavy

# 5, detailed description of Count

SELECT COUNT (salary) from employees; # Number of non-null lines in the Salary field

SELECT COUNT (*) from employees; # count (*), the total number of rows in the table (as long as there are 1 fields in a row are not NULL, count in)

SELECT COUNT (1) from employees; # equivalent to adding 1 column 1 to the table, counting the number of rows in the column (the actual number of rows in the table)

# 6, there are restrictions on the fields queried together with the grouping function
# logical table structure also want the rule (cannot 1 fields x row, 1 fields y line)

# Error Instance
SELECT AVG (Salary), employee_id from employees; # avg has only 1 lines, EMPLOYEE_ID has 107 lines, and the result must be a problem

05_mysql Common Functions _ Grouping functions