Example of using MySQL to count the number of different values ​​in a column

Preface

The requirement implemented in this article is actually very common. For example, we have a user source table to mark the channel from which the user registered. The table structure is shown below…

Origin is the user source, and its values ​​include iPhone, Android, and Web. Now we need to count the number of users registered by these three channels separately.

Solution 1

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

Use the where statement to count the respective quantities.

This is a bit too much to query. If there are 10 values, you have to write 10 similar statements, which is very troublesome.

Is there a single statement that can do it? So I went to look up some information.

Solution 2

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

Count the number of lines in user_operation_log:

SELECT count(*) FROM user_operation_log

Count the number of values ​​in the origin column that are not NULL:

SELECT count(origin) FROM user_operation_log

So we can use this feature to achieve the above requirements

The first way to write (using count)

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

Query results

The second way to write (using 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 Web
FROM user_operation_log;

Query results

The third way to write (rewrite sum)

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

Query results

The fourth way to write it (from the answer of Nuggets user Jeff)

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

Query results

So far, our needs have been met.

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.