Several ways to count MySQL by condition

Recently, in the background of a website to add a series of statistical functions, encountered a lot of conditions to be counted by the condition. Try several methods, the following summary records, for your reference.

Problem description

To make the discussion simple and understandable, I simplified the question a little, removing many backgrounds.

Once there was an emperor, he has 50 concubine, these concubine very does not have the justice to give him the birth of 100,000 sons, so, the emperor is very distressed, the massive son is difficult to manage, moreover, he wants to know each concubine gives him to have how many son, thus reward, this is very difficult. So, the emperor asked a programmer to help him make up a program, with the database to store all the information of the son, so that you can use the program to statistics and management.

The structure of the database is as follows:

Id	Prince's unique number
Mother	The only number of the prince's mother

The emperor divided the concubine into two grades, the heavenly Empress (number less than 25) and underground Palace Niang (number is greater than or equal to 25), he would like to know the heavenly Empress and underground palace Empress of fertility is strong or weak. So, the programmer began to write SQL query.

Method 1: Use Group Bysql Query

SELECT COUNT(*) FROM `prince` GROUP BY `mother` > 24;

Execution results

count(*)5002949971

Elapsed time on 100,000 rows of data: 0.0335 seconds

Analysis

The biggest problem with this group by approach is that it is impossible to distinguish between the results obtained. Which of the two numbers is the number of the Prince of the Heavenly Empress, which is the number of the underground palace empress born of the prince? I don't know. So, although it counts the totals, it doesn't make any sense.

Therefore, in order to distinguish the statistical results, the conditions Mother > 24 must also be shown as a field in the result set as a field, the modified SQL is as follows:

SELECT COUNT(*) AS `number`, `mother` > 24 AS `type` FROM `prince` GROUP BY `mother` > 24;

Execution results

number  type50029   049971   1

When a conditional expression is a field, the value of the field is the value of the conditional expression, so, for our example, type = 1 means that the value of Mother > 24 is 1, so the number in the second row represents the number of underground palace that the Empress had born.

After the modification, we see that the heavenly Empress notch above.

Advantages and Disadvantages

The disadvantage is obvious, because the use of conditional expressions as a group basis, it can only do two Yuan division, for the classification to be divided into multiple categories of statistics can not be competent. For example, to separate statistics 1~10 number, 11~24 number, 25th number of the concubine ~50 number, it will not be achieved.

In addition, because of the use of group BY, it involves ordering, which takes longer to execute.

I have not found the advantages of this method for the time being.

Method 2: Use a nested Select

Using nested select also achieves the purpose of counting the data under one condition in each SELECT clause, and then integrating the statistics with a master select.

SQL Query

SELECT     ( SELECT COUNT( * ) FROM `prince` WHERE `mother` >24 ) AS `digong`,     ( SELECT COUNT( * ) FROM `prince` WHERE `mother` <=24 ) AS `tiangong`

Execution results

digong  tiangong49971   50029

Elapsed time on 100,000 rows of data: 0.0216 seconds

Analysis

This method of nested select is very intuitive, that is, the statistics of the various conditions, the final summary, easy to understand, and natural language no difference.

Advantages and Disadvantages

The advantage is intuitive, and faster than group by. Although it is a 3 SELECT statement, it looks like 2 more statements than the group by scheme, but it does not involve sorting, which saves a lot of time.

The disadvantage may be a little more statements, the number of students who have neat cleanliness may be more uncomfortable.

Method 3: Use case when

The case-when statement is powerful enough to define flexible query conditions and is ideal for classifying statistics.

SQL Query

SELECT     COUNT( CASE WHEN `mother` >24 THEN 1 ELSE NULL END ) AS `digong`,     COUNT( CASE WHEN `mother` <=24 THEN 1 ELSE NULL END ) AS `tiangong`FROM prince

Execution results

digong  tiangong49971   50029

Elapsed time on 100,000 rows of data: 0.02365825 seconds

Analysis

The key to this approach is

Here the count and case are used in conjunction to achieve the classification count. When the case is used first, when the condition is met, the field value is set to 1, the field value is set to NULL when the condition is not met, and the Count function counts only the non-null field, and the problem is resolved.

Advantages and Disadvantages

The advantage is that this method does not involve sorting, so the run time is equivalent to Method 2, and the SELECT statement is reduced to 1.

The disadvantage is that the sentence is longer, the sentence length of the students who have neat cleanliness may be more uncomfortable.

Summarize

For determining the conditional count of a classification, you can avoid group by as much as possible, thus avoiding sorting actions and speeding up query execution.

If you need to classify the value of a field, and the value of the field is variable, such as the emperor to count each concubine's number of births, and he may continue to marry many more concubine, in this case, the use of Method 2 and Method 3 is not very good, or use a group by come simple and convenient.

Several ways to count MySQL by condition