MySQL count detailed explanation and function example code

Detailed explanation of mysql count

The count function is used to count the records in a table or array. Here I will introduce the usage of the count function in MySQL.

count(*) It returns the number of retrieved rows, whether or not they contain NULL values.

When the SELECT retrieves from one table without retrieving other columns and has no WHERE clause, COUNT(*) is optimized to return the fastest speed.

For example:

mysql> SELECT COUNT(*) FROM student;

COUNT(DISTINCT field) This optimization is only applicable to MyISAM tables, because these table types store the exact number of records returned by a function and are very easy to access.

For transactional storage engines (InnoDB, BDB), storing an exact row count is more problematic because multiple transactions may occur, each of which may affect the row count.

Returns the number of distinct non-NULL values.

If no matching item is found, COUNT(DISTINCT) returns 0.

example

Create a data table for testing to perform count data statistics:

CREATE TABLE `user` (
 `id` int(5) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(10) DEFAULT NULL,
 `password` varchar(10) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

 
The test data is:

1 name1 123456
2 name2 123456
3 name3 123456
4 name4 NULL

Please note the results returned by the following query

select count(*) from `user`
select count(name) from `user`
select count(password) from `user`

Output: 4,4,3

Cause Analysis:

1. count(*) counts the number of rows, so the result is 4.

2. count(column_name) counts the rows in the column that are not empty, so count(name)=4, and count(password)=3.

Pay attention to the above two points when using the count function.

Use GROUP BY to group all records for each owner. Without it, you will get the error message:

mysql> SELECT owner, COUNT(*) FROM pet;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...)
with no GROUP columns is illegal if there is no GROUP BY clause

COUNT() and GROUP BY sort your data in various ways. The following examples show different ways of conducting an animal census operation.

Number of each animal:

mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| bird | 2 |
| cat | 2 |
| dog | 3 |
| hamster | 1 |
| snake | 1 |
+---------+----------+

Number of animals of each sex:

mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
+------+----------+
| sex | COUNT(*) |
+------+----------+
| NULL | 1 |
| f | 4 |
| m | 4 |
+------+----------+

(In this output, NULL means "unknown gender.")

Number of animals by species and sex:

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 |
+---------+------+----------+

With COUNT(), you don't have to search the entire table. For example, the previous query, when performed only on dogs and cats, would be:

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 just need the number of animals by sex where the sex is known:

mysql> SELECT species, sex, COUNT(*) FROM pet
  -> WHERE sex IS NOT NULL
  -> 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 keyword of MySQL has many uses that you might not expect.

1. Can be used when counting unique records

For example, SELECT COUNT( DISTINCT id ) FROM tablename;

It is to calculate the number of records with different ids in the talbebname table

2. When you need to return the specific value of different IDs, you can use

For example, SELECT DISTINCT id FROM tablename;

Returns the specific values ​​of different ids in the talbebname table

3. The above situation 2 will be ambiguous when more than 2 columns of results need to be returned from the MySQL table

For example, SELECT DISTINCT id, type FROM tablename;

In fact, the result returned is that id and type are different at the same time, that is, DISTINCT acts on two fields at the same time, and only those with the same id and type are excluded, which is different from the result we expected.

4. At this time, you can consider using the group_concat function to exclude, but this MySQL function is only supported in MySQL 4.1 and above

5. In fact, there is another solution, which is to use

SELECT id, type, count(DISTINCT id) FROM tablename

Although the return result has an extra column of useless count data (perhaps you need this useless data I mentioned)

The returned result is all the results with different ids. The above 4 types can be used complementary to each other. It depends on what kind of data you need.

Thank you for reading, I hope it can help you, thank you for your support of this site!