Mysql下count()和sum()区别
发布:smiling 来源: PHP粉丝网 添加日期:2014-10-10 15:40:15 浏览: 评论:0
在mysql中有两个函数count()与sum()函数,有很多朋友搞不清楚,从英文的角度我们可以分析出来count是统计个数,sum是求各并且只能是数值型.
要求:查询出2门及2门以上不及格者的平均成绩。
经常会用两种查询语句有两种:
select name,sum(score < 60) ,avg(score) from result group by name having sum(score<60) >=2;
再看,算你拥有动物的总数目与“在pet表中有多少行?”是同样的问题,因为每个宠物有一个记录。COUNT(*)函数计算行数,所以计算动物数目的查询应为:
- mysql> SELECT COUNT(*) FROM pet;
- +----------+
- | COUNT(*) |
- +----------+
- | 9 |
- +----------+
在前面,你检索了拥有宠物的人的名字,如果你想要知道每个主人有多少宠物,你可以使用COUNT( )函数,代码如下:
- mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
- +--------+----------+
- | owner | COUNT(*) |
- +--------+----------+
- | Benny | 2 |
- | Diane | 2 |
- | Gwen | 3 |
- | Harold | 2 |
- +--------+----------+
注意,使用GROUP BY对每个owner的所有记录分组,没有它,你会得到错误消息,代码如下 :
mysql> SELECT owner, COUNT(*) FROM pet;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
COUNT( )和GROUP BY以各种方式分类你的数据,下列例子显示出进行动物普查操作的不同方式.
每种动物的数量,代码如下:
- mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
- +---------+----------+
- | species | COUNT(*) |
- +---------+----------+
- | bird | 2 |
- | cat | 2 |
- | dog | 3 |
- | hamster | 1 |
- | snake | 1 |
- +---------+----------+
每种性别的动物数量,代码如下:
- mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
- +------+----------+ --phpfensi.com
- | sex | COUNT(*) |
- +------+----------+
- | NULL | 1 |
- | f | 4 |
- | m | 4 |
- +------+----------+
在这个输 出中,NULL表示“未知性别”.
按种类和性别组合的动物数量,代码如下:
- mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
- +---------+------+----------+
- | species | sex | COUNT(*) |
- +---------+------+----------+
- | bird | NULL | 1 |
- | bird | f | 1 |
- | cat | f | 1 |
- | cat | m | 1 |
- | dog | f | 1 |
- | dog | m | 2 |
- | hamster | f | 1 |
- | snake | m | 1 |
- +---------+------+----------+
若使用COUNT( ),你不必检索整个表,例如,前面的查询,当只对狗和猫进行时,应为:
- mysql> SELECT species, sex, COUNT(*) FROM pet
- -> WHERE species = 'dog' OR species = 'cat'
- -> GROUP BY species, sex;
- +---------+------+----------+
- | species | sex | COUNT(*) |
- +---------+------+----------+
- | cat | f | 1 |
- | cat | m | 1 |
- | dog | f | 1 |
- | dog | m | 2 |
- +---------+------+----------+
或如果你仅需要知道已知性别的按性别的动物数目,代码如下:
- mysql> SELECT species, sex, COUNT(*) FROM pet
- -> WHERE sex IS NOT NULL
- -> GROUP BY species, sex;
- +---------+------+----------+
- | species | sex | COUNT(*) |
- +---------+------+----------+
- | bird | f | 1 |
- | cat | f | 1 | --phpfensi.com
- | cat | m | 1 |
- | dog | f | 1 |
- | dog | m | 2 |
- | hamster | f | 1 |
- | snake | m | 1 |
- +---------+------+----------+
mysql sum,代码如下:
select name,count((score<60)!=0) as a,avg(score) from result group by name having a >=2;
Tags: count() sum()区别
相关文章
- ·mysql中innodb表中count()优化(2015-04-17)
推荐文章
热门文章
最新评论文章
- 写给考虑创业的年轻程序员(10)
- PHP新手上路(一)(7)
- 惹恼程序员的十件事(5)
- PHP邮件发送例子,已测试成功(5)
- 致初学者:PHP比ASP优秀的七个理由(4)
- PHP会被淘汰吗?(4)
- PHP新手上路(四)(4)
- 如何去学习PHP?(2)
- 简单入门级php分页代码(2)
- php中邮箱email 电话等格式的验证(2)