Example of how to use MySQL to count the number of different values in a column

Objective

This is a very common requirement in this article, for example, we have a user-origin table that is used to mark the channel from which the user is registered. The table structure is as follows ...

Origin is the source of the user, the value of which is the IPhone, Android, Web three kinds, now need to count the number of users registered by these three channels.

Solutions 1

?

123456789

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 count the respective numbers separately.

So the amount of the query is a bit more, if this value has 10, it will have to write 10 similar statements, very troublesome.

Is there a statement that's going to fix it? So I went to look up some information.

Solutions 2

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

Statistics User_operation_log How many lines:

?

1	SELECTcount(*) FROMuser_operation_log

Statistics origin This column value is not a NULL number:

?

1	SELECTcount(origin) FROMuser_operation_log

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

First notation (implemented with count)

?

12345

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 results

Second notation (implemented with sum)

?

12345

SELECT sum(if(origin = ‘iPhone‘, 1, 0)) ASiPhone, sum(if(origin = ‘Android‘, 1, 0)) AS Android, sum(if(origin = ‘Web‘, 1, 0))  AS WebFROMuser_operation_log;

Query results

Third notation (rewrite sum)

?

12345

select   sum (origin = IPhone ' ) as iphone,   sum ' Android ' as android,   sum (origin = ' Web ' )   as web Code class= "SQL keyword" >from user_operation_log;

Query results

Fourth notation (from the Nuggets user Jeff's answer)

?

1	SELECTorigin,count(*) num FROM user_operation_log GROUP BYorigin;

Query results

At this point, we have reached our needs.

Summarize

The above is the entire content of this article, I hope that the content of this article on everyone to learn or work can bring certain help, if there are questions you can message exchange, thank you for the script home support.

Example of how to use MySQL to count the number of different values in a column