进阶5:分组函数

#进阶五:分组函数
/*
功能：用作统计使用，又称聚合函数或统计函数或组函数

分类：
求和 sum
平均值 avg
最大值 max
最小值 min
个数 count
特点：
1、 sum,avg一般只能用于数值型
max、min、count既可以处理数值型也可以处理字符型
2、 是否忽略null
所有的分组函数都忽略null
3、和distinct搭配去重后运算
4、count函数的详细使用
效果：
myisam存储引擎，count()的效率高
innodb存储引擎，count()和count(1)的效果差不多，比count（字段）要高一些
统计行数：
count(*)
count(常量)：count(0)/count(1)/count(‘玉林’)
5、和分组函数一同查询的字段要求是group by后的字段

*/

USE myemployees;
#1、简单使用

 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) 和,
	AVG(salary) 平均值,
	MAX(salary) 最大,
	MIN(salary) 最小,
	COUNT(salary) 数量
FROM
	employees;

 SELECT 
	SUM(salary) 和,
	ROUND(AVG(salary),2) 平均值,
	MAX(salary) 最大,
	MIN(salary) 最小,
	COUNT(salary) 数量
FROM
	employees;

#2、参数支持哪些类型

#错误
SELECT SUM(last_name) FROM employees;
SELECT AVG(last_name) FROM employees;
#正确
SELECT MAX(last_name) FROM employees;
SELECT MIN(last_name) FROM employees;
SELECT COUNT(last_name) FROM employees;
SELECT COUNT(commission_pct) FROM employees;
SELECT SUM(hiredate) FROM employees;
SELECT MAX(hiredate) FROM employees;
SELECT COUNT(hiredate) FROM employees;

#3、是否忽略null

SELECT 
	SUM(commission_pct),
	AVG(commission_pct),
	SUM(commission_pct) / 35,
	AVG(commission_pct) / 35
FROM
	employees;
SELECT
	MAX(commission_pct)
FROM
	employees;
SELECT
	COUNT(commission_pct)
FROM
	employees;
SELECT
	commission_pct
FROM
	employees;

#4、和distinct搭配

SELECT 
    SUM(DISTINCT salary),
    SUM(salary) 
FROM
    employees ;
SELECT
	COUNT(DISTINCT salary),
	COUNT(salary)
FROM
	employees;

#5、count函数的详细使用

SELECT
	COUNT(salary)
FROM
	employees;
SELECT
	COUNT(*)
FROM
	employees;
SELECT
	COUNT(1)
FROM
	employees;
SELECT
	COUNT(0)
FROM
	employees;

#6、和分组函数一同查询的字段有限制

#错误
SELECT
	AVG(salary),
	employee_id
FROM
	employees;