mysql count Detailed Explanation and Function Instance Code

mysql count Explanation

The count function is a function used to count records in tables or arrays. I will introduce the usage of count function in mysql below.

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

COUNT (*) is optimized for the fastest return speed when SELECT retrieves from one table and no other columns, and there is no WHERE clause.

For example:

mysql> SELECT COUNT(*) FROM student;

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

For transactional storage engines (InnoDB, BDB), there are many problems in storing an exact number of rows, because multiple transactions may occur, and each of them may have an impact on the number of rows.

Returns the number of different non-NULL values.

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

Example

Create a data table for testing to perform count 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 are: 

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

Note the return results of 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 non-empty rows in a column, so count (name) = 4 and count (password) = 3.

The above two points should be paid attention to when using count function.

Use GROUP BY to group all records for each owner. Without it, you get an 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 classify your data in various ways. The following examples show different ways of conducting animal census operations.

Number of animals per species:

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

Number of animals per 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 retrieve the entire table. For example, the previous query, when it is only for dogs and cats, should 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 only need to know the number of animals by sex with known sex:

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 keywords of mysql's DISTINCT have many unexpected uses

1. Used when count does not duplicate records

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

Is to calculate the number of id different records in the talbebname table

2. When you need to return the specific values of recording different id, you can use

For example, SELECT DISTINCT id FROM tablename;

Returns the specific values of different id in the talbebname table

3. Scenario 2 above is ambiguous when you need to return more than 2 columns from the mysql table

For example, SELECT DISTINCT id, type FROM tablename;

In fact, id and type are not the same results, that is, DISTINCT acts on two fields at the same time, id and tyoe must be the same before being excluded, which is different from the expected results

4. Consider using the group_concat function for exclusion, but this mysql function is supported above mysql 4.1

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

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

Although this return result has an extra column of useless count data (maybe you need this useless data)

The result returned is that only id is different from all the results and the above 4 types can be used complementarily, depending on what kind of data you need

Thank you for reading, hope to help everyone, thank you for your support to this site!