mysql中count函数和sum函数的注意事项

聚合函数

聚合函数是用来做纵向运算的函数：

COUNT()：统计指定列不为NULL的记录行数；

SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

count()函数中不能加表达式，加了表达式不会执行，表达式只能加到where后；

sun（）函数中可以加表达式，但是加了表达式后会得到该列满足该表达式的非空个数统计和，和count()函数在后面加表达式的结果一样。

mysql> select sal from emp;
+---------+
| sal     |
+---------+
|  800.00 |
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 1250.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 5000.00 |
| 1500.00 |
| 1100.00 |
|  950.00 |
| 3000.00 |
| 1300.00 |
+---------+
14 rows in set (0.00 sec)

mysql> select count(sal>2500) from emp;
+-----------------+
| count(sal>2500) |
+-----------------+
|              14 |
+-----------------+
1 row in set (0.00 sec)

mysql> select count(*) from emp where sal>2500;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)


mysql> select sum(sal) from emp where sal>2500;
+----------+
| sum(sal) |
+----------+
| 16825.00 |
+----------+
1 row in set (0.00 sec)

mysql> select sum(sal>2500) from emp;
+---------------+
| sum(sal>2500) |
+---------------+
|             5 |
+---------------+
1 row in set (0.00 sec)

mysql> select count(*) from emp where sal>2500;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)


mysql> select sum(sal) from emp where sal>2500;
+----------+
| sum(sal) |
+----------+
| 16825.00 |
+----------+
1 row in set (0.00 sec)

mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
1 row in set (0.00 sec)

mysql> select comm from emp;
+---------+
| comm    |
+---------+
|    NULL |
|  300.00 |
|  500.00 |
|    NULL |
| 1400.00 |
|    NULL |
|    NULL |
|    NULL |
|    NULL |
|    0.00 |
|    NULL |
|    NULL |
|    NULL |
|    NULL |
+---------+
14 rows in set (0.00 sec)

mysql> select sum(comm>400) from emp;
+---------------+
| sum(comm>400) |
+---------------+
|             2 |
+---------------+
1 row in set (0.00 sec)

mysql> select sum(comm<400) from emp;
+---------------+
| sum(comm<400) |
+---------------+
|             2 |
+---------------+
1 row in set (0.00 sec)

mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

mysql> select count(comm) from emp where comm<400;
+-------------+
| count(comm) |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)