表记录查询中的聚合函数

聚合函数

COUNT(列名)，统计所有行数，但是不包括null

• 统计有多少员工select count(id) from em;

+-----------+
| count(id) |
+-----------+
|         7 |
+-----------+

• 统计基本工资小于10000元的员工个数select count(name) from em where salary < 10000;

+-------------+
| count(name) |
+-------------+
|           2 |
+-------------+

AVG(列名)，统计所在列的平均数，个数和不包括null所在的列

• select avg(bouns) from em;

+------------+
| avg(bouns) |
+------------+
|   775.0000 |
+------------+

• select avg(ifnull(bouns,0)) from em;

+----------------------+
| avg(ifnull(bouns,0)) |
+----------------------+
|             664.2857 |
+----------------------+

SUM(列名)，统计符合筛选条件的行的总和

• 统计每个部门奖金和基本工资的总和，elect dp,sum(ifnull(bouns,0)+salary) from em group by dp;

+-------+-----------------------------+
| dp    | sum(ifnull(bouns,0)+salary) |
+-------+-----------------------------+
| NULL  |                       23350 |
| 2345  |                       23500 |
| sales |                       22400 |
| IT    |                       15400 |
+-------+-----------------------------+

MAX(列名)、MIN(列名)

• 统计薪资最低的员工，select name,(ifnull(bouns,0)+salary) as total from em order by total limit 1;

+------+-------+
| name | total |
+------+-------+
| Sasa |  8000 |
+------+-------+