Use MySQL to calculate the number of different values in a column.

Use MySQL to calculate the number of different values in a column.

Preface

This requirement is quite common in this article. For example, we have a user source table to mark the channel from which the user registers. The table structure is as follows...

The origin is the user source, which has three values: iPhone, Android, and Web. Now we need to calculate the number of users registered through these three channels.

Solution 1

SELECT count(*)FROM user_operation_logWHERE origin = 'iPhone';SELECT count(*)FROM user_operation_logWHERE origin = 'Android';SELECT count(*)FROM user_operation_logWHERE origin = 'Web';

Use the where statement to calculate the respective quantity.

In this way, the number of queries is a little large. If there are 10 queries, you have to write 10 similar statements, which is very troublesome.

Is there a statement to fix it? So I checked some information.

Solution 2

We know that count can be used to count not only the number of rows, but also the number of column values, for example:

Count the number of rows in user_operation_log:

SELECT count(*) FROM user_operation_log

Count the number of non-NULL values in the origin column:

SELECT count(origin) FROM user_operation_log

So we can use this feature to meet the above requirements.

Method 1 (implemented using count)

SELECT count(origin = 'iPhone' OR NULL) AS iPhone, count(origin = 'Android' OR NULL) AS Android, count(origin = 'Web' OR NULL)  AS WebFROM user_operation_log;

Query Result

Method 2 (implemented by sum)

SELECT sum(if(origin = 'iPhone', 1, 0)) AS iPhone, sum(if(origin = 'Android', 1, 0)) AS Android, sum(if(origin = 'Web', 1, 0))  AS WebFROM user_operation_log;

Query Result

Method 3 (rewrite sum)

SELECT sum(origin = 'iPhone') AS iPhone, sum(origin = 'Android') AS Android, sum(origin = 'Web')  AS WebFROM user_operation_log;

Query Result

Fourth Writing Method (answer from nuggets user Jeff)

SELECT origin,count(*) num FROM user_operation_log GROUP BY origin;

Query Result

So far, we have met our needs.

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message. Thank you for your support.