Aggregate function

Aggregate function is a function used for vertical operation:

COUNT(): count the number of record rows whose specified column is not NULL;

SUM(): calculates the sum of values of the specified column. If the specified column type is not a numeric type, the calculation result is 0;

An expression cannot be added to the count() function. If an expression is added, it will not be executed. The expression can only be added after where;

You can add an expression to the sun() function, but after you add an expression, you will get the non empty statistics sum of the column satisfying the expression, which is the same as the result of adding an expression to the count() function.

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)