关于SQL查询，count, sum, group by 的一些总结

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中的字段名不用和分组字段名相同