How to use the Count function in MySQL

Source: Internet
Author: User
Tags one table

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.