MySQL SUM () COUNT ()

Title MySQL SUM () COUNT () is also in the work of the record

SUM () returns the number of columns and the total number of points such as a student

Eg:select sum (obj) from table is definitely the sum of the numbers of the obj columns in the table tables

Count () calculates the number of the specified column and a number of failed subjects such as a student

Eg:select Count (*) from table is definitely the number of rows in table tables where NULL is ignored

Sum is often used together with group by count and distinct

Like the student watch below.

Ysql> Show CREATE TABLE Sc\g
1. Row ***************************
Table:sc
Create table:create Table ' SC ' (
' Sid ' varchar () DEFAULT NULL,
' Cid ' varchar 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 |
+------+------+-------+
Rows in Set (0.01 sec)

Mysql>

Count the failed SID and the total score.

1 Select Sid,sum (score) from SC where score < GROUP by SID;

2 Select Sid,count (score) from SC where score < GROUP by SID;

Execution Result:

Mysql> Select Sid,sum (score) from SC where score < 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 < GROUP by SID;
+------+--------------+
| Sid | Count (Score) |
+------+--------------+
|            04 | 3 |
|            06 | 2 |
+------+--------------+
2 rows in Set (0.00 sec)

Mysql>
In other words, the difference can be seen

The argument in the Sum () function is the addition of the value of the column name when the column name is calculated

Count () The number of times a value entry is calculated when the parameter in the function is a column name

Mysql> Select Sid,sum (Score <) from the SC group by SID;
+------+-----------------+
| sid  | sum (Score <) |
+------+-----------------+
| 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 <) from the 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>

This article is from "Yun Weibang" blog, please be sure to keep this source http://aklaus.blog.51cto.com/9724632/1597488

MySQL SUM () COUNT ()