MySQL SUM() COUNT()
如题MySQL SUM() COUNT() 也是在工作中碰到的 特此记录一下SUM（）返回的是列的数字和 如一个学生的总分数
eg：select sum(obj) from table 返回的肯定是table表中 obj列的数字之和
COUNT() 计算指定列的个和 如一个学生的不及格科目几科
eg：select count(*) from table 返回的肯定是table表中有多少行 null是被忽略的
sum经常和group by一起使用 count经常和distinct使用
比如下边的这个学生表
ysql> show create table SC\G
*************************** 1. row ***************************
Table: SC
Create Table: CREATE TABLE `SC` (
`Sid` varchar(10) DEFAULT NULL,
`Cid` varchar(10) DEFAULT NULL,
`score` decimal(18,1) DEFAULT NULL,
KEY `Sid` (`Sid`),
KEY `Cid` (`Cid`),
CONSTRAINT `SC_ibfk_1` FOREIGN KEY (`Sid`) REFERENCES `Student` (`Sid`),
CONSTRAINT `SC_ibfk_2` FOREIGN KEY (`Cid`) REFERENCES `Course` (`Cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from SC;
+------+------+-------+
| Sid| Cid| score |
+------+------+-------+
| 01 | 01 |80.0 |
| 01 | 02 |90.0 |
| 01 | 03 |99.0 |
| 02 | 01 |70.0 |
| 02 | 02 |60.0 |
| 02 | 03 |80.0 |
| 03 | 01 |80.0 |
| 03 | 02 |80.0 |
| 03 | 03 |80.0 |
| 04 | 01 |50.0 |
| 04 | 02 |30.0 |
| 04 | 03 |20.0 |
| 05 | 01 |76.0 |
| 05 | 02 |87.0 |
| 06 | 01 |31.0 |
| 06 | 03 |34.0 |
| 07 | 02 |89.0 |
| 07 | 03 |98.0 |
+------+------+-------+
18 rows in set (0.01 sec)
mysql>
统计一下成绩不及格的Sid及总成绩
1 select sid,sum(score) from SC where score < 60 group by sid ;
2 select sid,count(score) from SC where score < 60 group by sid;
执行结果：
mysql> select sid,sum(score) from SC where score < 60 group by sid;
+------+------------+
| sid| sum(score) |
+------+------------+
| 04 | 100.0 |
| 06 | 65.0 |
+------+------------+
2 rows in set (0.03 sec)
mysql> select sid,count(score) from SC where score < 60 group by sid;
+------+--------------+
| sid| count(score) |
+------+--------------+
| 04 | 3 |
| 06 | 2 |
+------+--------------+
2 rows in set (0.00 sec)
mysql>
也就是说 下面可以看出区别
Sum（）函数里面的参数是列名的时候 是计算列名的值的相加
Count()函数里面的参数是列名的的时候,那么会计算有值项的次数
mysql> select sid,sum(score < 60) from SC group by sid;
+------+-----------------+
| sid| sum(score < 60) |
+------+-----------------+
| 01 | 0 |
| 02 | 0 |
| 03 | 0 |
| 04 | 3 |
| 05 | 0 |
| 06 | 2 |
| 07 | 0 |
+------+-----------------+
7 rows in set (0.01 sec)
mysql> select sid,count(score < 60) from SC group by sid;
+------+-------------------+
| sid| count(score < 60) |
+------+-------------------+
| 01 | 3 |
| 02 | 3 |
| 03 | 3 |
| 04 | 3 |
| 05 | 2 |
| 06 | 2 |
| 07 | 2 |
+------+-------------------+
7 rows in set (0.02 sec)
mysql>
页:
[1]