The difference between count () and sum () in Mysql is described in detail _mysql

First create a table to explain the problem

Copy Code code as follows:

CREATE TABLE ' Result ' (
' name ' varchar default NULL,
' Subject ' varchar default NULL,
' Score ' tinyint (4) Default NULL
) Engine=myisam DEFAULT Charset=utf8

Insert some data,

Copy Code code as follows:

INSERT INTO result values
(' John ', ' mathematics ', 90),
(' John ', ' language ', 50),
(' John ', ' geography ', 40),
(' Dick ', ' language ', 55),
(' Dick ', ' politics ', 45),
(' Harry ', ' politics ', 30),
(' Zhao Liu ', ' language ', 100),
(' Zhao Liu ', ' mathematics ', 99),
(' Zhao Liu ', ' character ', 98);

Requirements: Check out the average score of 2 and 2 people who fail.
There are two types of query statements that are often used:

Copy Code code as follows:

Select Name,sum (Score <), AVG (score) from result group by name have sum (score<60) >=2;

Copy Code code as follows:

Select Name, Count ((score<60)!=0) as A,avg (score) from result group by name has a >=2;

The results of the two queries are certainly not the same, certainly the first correct, why, then 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 values is computed.
When the parameter in the Sum () function is the column name, it is the addition of the value of the computed column name, not the total number of the items that are valued.
Also note for count () lines: It calculates the total number of rows. Whether or not you have a value will be included in the calculation range. Another point: The Mysqlisam engine is easy to get statistics on the total number of rows. Query speed becomes faster
Summary: It is often used to count total rows in the actual programming. COUNT (*) is used at this time to be visible more than once. I rarely see anyone using a column name as an argument: count (a). Even in this way, it may be the intention to count the number of lines. I just don't know! This makes for subtle differences and uses the form of "column name" incorrectly.