Using count to add conditional statistics in Mysql

The general use of the count () function in Mysql is to count the number of records in a non-empty field, so you can use this feature to carry out conditional statistics. Note that if the field is NULL, it will not be counted, but false will be counted. With this in mind, let's take a look at several common conditional statistics.

Test environment

Windows 10

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 7

Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright ©2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Preparatory work

Create a new Mysql data table a containing id and num fields

Mysql > create table a (id int, num int)

Query OK, 0 rows affected (0.04 sec)

To insert test data, to see the effect of the count () function, we insert two empty data

Mysql > insert into a values (1100), (2200), (3300), (4300), (8), (9)

Query OK, 6 rows affected (0.01sec)

Records: 6 Duplicates: 0 Warnings: 0

Query the data in table an and compare it with the following statistics

Mysql > select * from a

| | id | num |

| | 1 | 100 | |

| | 2 | 200 |

| | 3 | 300 |

| | 4 | 300 |

| | 8 | NULL |

| | 9 | NULL |

6 rows in set (0.09 sec)

Call the count () function to see the effect. If you use count (*), you will query all the records, but if you use count (num) to find that there are only four pieces of data, the records with num as NULL are not statistically significant.

Mysql > select count (*) from a

| | count (*) |

| | 6 |

1 row in set (0.03 sec)

Mysql > select count (num) from a

| | count (num) |

| | 4 |

1 row in set (0.04 sec)

Conditional statistics Wuxi × × hospital https://yyk.familydoctor.com.cn/20612/

The conditional expression plus or null is used in the count () function, which means that when the condition is not satisfied, the function becomes count (null) and will not count the quantity.

Mysql > select count (num > 200or null) from a

| | count (num > 200 or null) | |

| | 2 |

1 row in set (0.22 sec)

The if expression is used in the count () function. When the condition is satisfied, the value of the expression is not empty, and the expression value is NULL when the condition is not satisfied.

Mysql > select count (if (num > 200,1, null)) from a

| | count (if (num > 200,1, null)) |

| | 2 |

1 row in set (0.05sec)

The case when expression is used in the count () function. When the condition is satisfied, the result of the expression is non-empty, and if the condition is not satisfied, no result defaults to NULL.

Mysql > select count (case when num > 200 then 1 end) from a

| | count (case when num > 200 then 1 end) | |

| | 2 |

1 row in set (0.07 sec)

Summary

Using the count () function to realize conditional statistics is based on the fact that records with a value of NULL are not counted. There are three common ways to assume that the statistical num is greater than 200.

Select count (num > 200 or null) from a

Select count (if (num > 200,1, null)) from a

Select count (case when num > 200 then 1 end) from a