With Count (*) or count (column name) | | Count () differs from sum () in MySQL

Count () differs from sum () in MySQL

First, create a table to illustrate the problem create table ' result ' (' name ' varchar () default NULL, ' subject ' varchar () default NULL, ' Score ' tinyint ( 4) Default NULL) Engine=myisam default Charset=utf8 inserts some data, insert into result values (' Zhang San ', ' math ', 90), (' Zhang San ', ' language ', 50), (' Zhang San ') , ' geography ', 40), (' John Doe ', ' language ', 55), (' John Doe ', ' 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 or more failed persons. There are two types of query statements that are often used: 1. Select Name,sum (Score <), AVG (score) from result group by name have sum (score<60) >=2;2.select name, Count ((score<60)!=0) as A,avg (score) from result group by name have a >=2; The results of the two queries must be different, certainly the first one, and the reason why, then you have to think about the meaning of the count () function and the meaning of the SUM function The count () function evaluates to the number of times a value item is evaluated when the argument is a column name. (NULL does not count, but "value is counted")
Count ( *) can calculate the number of trips, COUNT (1) can also calculate the number of trips, 1 here represents a row. The count () function evaluates to the number of times a value item is evaluated when the argument is a column name.
Count (*) is much faster than count ([Column Name]),count (conditional expression), regardless of whether the record satisfies the conditional expression, plus 1 if non-null; The argument in the Sum () function is the column name, which is the sum of the values of the computed column names, not the total number of values. SUM (conditional expression), plus 1 if the record satisfies the condition expression The argument in the Sum () function is the column name, which is the sum of the values of the computed column names, not the total number of values. When the column value of a record is null, COUNT (column name) and sum (column name) do not count for this record. Summary: The total number of statistics in actual programming is often used. At this point, multiple places are visible using count (*). I seldom see a case where a column name is used as a parameter: count (a). Even if you use this, you may want to count the number of rows. Just don't know! This makes the subtle difference and incorrectly uses the "column name" form.

With Count (*) or count (column name) | | Count () differs from sum () in MySQL