Difference between count () and sum () in Mysql _ MySQL

Difference between count () and sum () in Mysql bitsCN.com

First, create a table to describe the problem.

Create table 'result '(

'Name' varchar (20) default NULL,

'Subobject' varchar (20) default NULL,

'Score 'tinyint (4) default NULL

) ENGINE = MyISAM default charset = utf8

Insert some data,

Insert into result values

('Zhang San', 'mat', 90 ),

('Zhang San', 'China', 50 ),

('Zhang San', 'Geo', 40 ),

('Li Si', 'China', 55 ),

('Li Si', 'political ', 45 ),

('Wang 5', 'political ', 30 ),

('Zhao Liu', 'language', 100 ),

('Zhao Liu', 'mat', 99 ),

('Zhao Liu', 'morality ', 98 );
Requirement: query the average score of two or more non-pass students.
Two query statements are often used:
1. select name, sum (score <60), avg (score) from result group by name having sum (score <60)> = 2;
2. select name, count (score <60 )! = 0) as a, avg (score) from result group by namehaving a> = 2;

The results of the two queries must be different. they must be the first correct one. The reason is why, now you have to think about the meaning of the count () function and the meaning of the sum function.

When the parameters in the count () function are column names, the number of times a value item is calculated.

When the parameters in the Sum () function are column names, they are used to calculate the Sum of column names, rather than the total number of values.

Pay attention to the number of count () rows: it calculates the total number of rows. Whether or not you have a value is included in the calculation range. Another point: the mysqlisam engine can easily obtain statistics on the total number of rows. Faster query speed
Summary: It is often used to count the total number of rows in actual programming. In this case, count (*) is used for multiple visibility. I rarely see the use of column names as parameters: count (. Even in this way, the original intention may be to count the number of rows. I just don't know! The form of "column name" is incorrect due to the slight difference.

BitsCN.com