SQL query example with count or sum as the condition (SQL Query count)

For example, the user_num table:

Example 1:Query the user that appears twice

Beginners often mistakenly think that the count () algorithm is directly used in the where statement. Obviously, this idea is wrong. The count () method cannot be used in the where clause, to solve the problem, we can use HAVING next to the group by clause as the condition restriction.
Incorrect practice: select * from user_num where count (user)> = 2 group by user;
Correct practice: select * from user_num group by user HAVING count (user)> = 2;
Explanation: HAVING is similar to WHERE and can be used to determine which records to select. The HAVING clause specifies in the SELECT statement which records have been grouped by group by clause are displayed. After group by combines records, HAVING displays any records that meet the HAVING clause GROUP.

Example 2:Query users whose num is greater than 10 for a single user

With prior experience, write the sum () method in the HAVING clause.
Correct practice: select * from user_num group by user HAVING sum (num)> 10;

Note: a having clause can contain up to 40 expressions. The expressions in the HAVING clause can be separated by and or.