count ,总的来说,count是为了获得查询所得的总数量或者是分组后每个组的数量
select count(*) from tbl;
得到tbl表的行数,一般来说结果如下
+----------+
| count(*) |
+----------+
| 5 |
+----------+
select count(*) from tbl group by name with rollup
得到分组查询后每个组的数量,结果如下
+----------+
| count(*) |
+----------+
| 1 |
| 2 |
| 1 |
| 1 |
| 5 |
+----------+
加上rollup可以统计总的数量,即最后一行所示,count(*)若要和group by 配合使用,select 后可以不选择任何字段,若要选择字段必须是group by 后的字段,否则会出错
select count(*), name from users group by name with rollup;
结果如下
+----------+-----------+
| count(*) | name |
+----------+-----------+
| 1 | aaa |
| 2 | shanyikun |
| 1 | syk |
| 1 | tiantian |
| 5 | NULL |
+----------+-----------+
可以使用count(distinct name)计算不重复的name数量
select count(distinct name) from users;
结果如下
+----------------------+
| count(distinct name) |
+----------------------+
| 4 |
+----------------------+
也可以加上分组显示
select count(distinct name) ,name from users group by name;
结果如下
+----------------------+-----------+
| count(distinct name) | name |
+----------------------+-----------+
| 1 | aaa |
| 1 | shanyikun |
| 1 | syk |
| 1 | tiantian |
+----------------------+-----------+
sum一般用于统计可相加的字段sum(age)这样的
select sum(id),name from users group by name;
结果如下
+---------+-----------+
| sum(id) | name |
+---------+-----------+
| 6 | shanyikun |
| 2 | syk |
| 3 | tiantian |
| 4 | aaa |
+---------+-----------+
sum中的字段名不用和分组字段名相同