SQL Lesson 10: 在查询中进行统计I

常见统计函数

函数	函数解释 （column 指列名）
COUNT(*) 和 COUNT(column)	COUNT(*) 统计全部的数据行数，COUNT(column) 统计column非NULL的行数
MIN(column)	找column最小的一行
MAX(column)	找column最大的一行
AVG(column)	对column所有行取平均值
SUM(column)	对column所有行求和

练习

数据表：employees

Role	Name	Building	Years_employed
Engineer	Becky A.	1e	4
Engineer	Dan B.	1e	2
Engineer	Sharon F.	1e	6
Engineer	Dan M.	1e	4
Engineer	Malcom S.	1e	1
Artist	Tylar S.	2w	2
Artist	Sherman D.	2w	8
Artist	Jakob J.	2w	6
Artist	Lillia A.	2w	7
Artist	Brandon J.	2w	7
Manager	Scott K.	1e	9
Manager	Shirlee M.	1e	3
Manager	Daria O.	2w	6
Engineer	Yancy I.	null	0
Artist	Oliver P.	null	0

1. 找出就职年份最高的雇员(列出雇员名字+年份）

SELECT Name, MAX(Years_employed)
FROM employees;

2.按角色(Role)统计一下每个角色的平均就职年份

SELECT Role, AVG(Years_employed)
FROM employees
GROUP BY Role

3.按办公室名字总计一下就职年份总和

SELECT Building,SUM(Years_employed)
FROM employees
WHERE Building IS NOT NULL
GROUP BY Building

4.每栋办公室按人数排名,不要统计无办公室的雇员

SELECT Building, COUNT(Name)
FROM employees
WHERE Building IS NOT NULL
GROUP BY Building
ORDER BY COUNT(Name)