How to use the Count function in MySQL

COUNT (*) it returns the number of rows retrieved, regardless of whether they contain null values.
SELECT retrieves from one table without retrieving other columns, and when there is no WHERE clause, COUNT (*) is optimized to the fastest return speed.

For example:

The code is as follows	Copy Code
Mysql> SELECT COUNT (*) from student;

This optimization applies only to MyISAM tables, because these table types store a function that returns the exact number of records and is very easy to access.
For a transaction-type storage engine (InnoDB, BDB), there are more problems storing an exact number of rows because of the possibility of multiple things being handled,
Each of these may have an effect on the number of rows.

COUNT (DISTINCT field)
Returns the number of different non-null values.
If no matching items are found, COUNT (DISTINCT) returns 0.

Cases

Create a data table for testing for count data statistics:

The code is as follows	Copy Code
CREATE TABLE ' user ' ( ' ID ' int (5) unsigned not NULL auto_increment, ' Name ' varchar (a) DEFAULT NULL, ' Password ' varchar DEFAULT NULL, PRIMARY KEY (' id ') ) Engine=myisam auto_increment=4 DEFAULT charset=latin1 Test data is: 1 name1 123456 2 name2 123456 3 Name3 123456 4 Name4 NULL

Note The return result of the following query:

The code is as follows	Copy Code
1,select Count (*) from ' user ' 2,select count (name) from ' user ' 3,select count (password) from ' user '

Output Result: 4,4,3

Reason Analysis:

1,count (*) is a count of the number of rows, so the result is 4.
2,count (column_name) is a row that is not empty in the column, so count (name) = 4, and count (password) = 3.
Two points above, be aware when using the Count function.

Use GROUP by to group all records for each owner, without which you get an error message:

The code is as follows	Copy Code
Mysql> SELECT owner, COUNT (*) from pet; ERROR 1140 (42000): Mixing of GROUP columns (MIN (), MAX (), COUNT (),...) With no group columns are illegal if there is no GROUP BY clause

COUNT () and group by classify your data in various ways. The following examples show different ways of conducting animal census operations.

Number of animals per species:

The code is as follows	Copy Code
Mysql> SELECT species, COUNT (*) from the pet GROUP by species; +---------+----------+ | Species | COUNT (*) | +---------+----------+ |        Bird | 2 | |        Cat | 2 | |        Dog | 3 | |        Hamster | 1 | |        Snake | 1 | +---------+----------+

Number of animals per sex:

The code is as follows	Copy Code
mysql> SELECT Sex, COUNT (*) from the pet GROUP by sex; +------+----------+ | sex | COUNT (*) | +------+----------+ |        NULL | 1 | |        f | 4 | |        m | 4 | +------+----------+ (in this output, null denotes "unknown sex.") ）

Number of animals by type and sex:

The code is as follows	Copy Code
mysql> SELECT species, Sex, COUNT (*) from pet GROUP by species, sex; +---------+------+----------+ | Species | sex | COUNT (*) | +---------+------+----------+ | Bird |        NULL | 1 | | Bird |        f | 1 | | Cat |        f | 1 | | Cat |        m | 1 | | Dog |        f | 1 | | Dog |        m | 2 | | Hamster |        f | 1 | | Snake |        m | 1 | +---------+------+----------+

If you use COUNT (), you do not have to retrieve the entire table. For example, the previous query, when only for dogs and cats, should be:

The code is as follows	Copy Code
mysql> SELECT species, Sex, COUNT (*) from pet -> WHERE species = ' dog ' OR species = ' cat ' -> GROUP by species, sex; +---------+------+----------+ | Species | sex | COUNT (*) | +---------+------+----------+ | Cat |        f | 1 | | Cat |        m | 1 | | Dog |        f | 1 | | Dog |        m | 2 | +---------+------+----------+

Or, if you only need to know the number of sex-disaggregated animals known as sex:

code is as follows

copy code

Mysql> SELECT species, Sex, COUNT (*) from pet    -> WHERE sex are not NULL   &nb Sp -> GROUP by species, sex; +---------+------+----------+ | species | sex  | COUNT (*) | +---------+------+----------+ | bird    | f    |         1 | | cat     | f    |        1 | | cat     | m    |        1 | | dog     | f    |        1 | | dog     | m    |        2 | | hamster | f    |        1 | | snake   | m    |        1 | +---------+------+----------+

by the way, the distinct of MySQL has a lot of useful things you can't imagine.

1. Can be used when count does not duplicate a record
For example, select COUNT (DISTINCT ID) from TableName;
is to calculate the number of records with different IDs in the Talbebname table.

2, when you need to return a record of the specific values of different IDs can be used
For example, select DISTINCT ID from tablename;
Returns the specific values of different IDs in the Talbebname table

3. The above situation 2 is ambiguous when you need to return the results of more than 2 columns in the MySQL table.
For example, select DISTINCT ID, type from tablename;
Actually returns the result that ID and type are not the same at the same time, that is, distinct two fields at the same time, must have ID and Tyoe are the same to be excluded, and we expect the result is not the same

4. You can consider using the GROUP_CONCAT function to exclude this time, but this MySQL function is supported above mysql4.1

5. In fact, there is another way to solve the problem is to use
SELECT ID, type, COUNT (DISTINCT ID) from tablename
Although the result of this return is a list of useless count data (perhaps you need this useless data I said)
The result of the return is that all the results with different IDs and 4 types above can be used in a complementary way to see what kind of data you need.