MySQL query statistics per SELECT statement

MySQL query statistics for a single SELECT statement is often complex in writing statements. The following describes how to use only one SELECT statement to help you.

 

Where can I use a single SELECT statement to implement MySQL query statistics? It is too useful. For example, if you want to query the number of people who pass or fail, how can you query it?

The simple statement for MySQL query statistics must be as follows:

1. Select a. Name, count_neg, count_plus from
2. (Select count (ID) as count_plus, name from score2 where score>= 60 group by name),
3. (Select count (ID) as count_neg, name from score2 where score<= 60 group by name) B
4. WhereA. Name=B. Name

That is, at least two statements must be used.

Today, we just found that MySQL supports if, so we can use if to implement it creatively:

1. Select name, sum (if (score>= 60, 1, 0), sum (if (score<60, 1, 0) from score2 group by name

It is easy to use a single SELECT statement to implement MySQL query statistics.

If the principle is greater than 60, the value is 1, and sum is the count.