MySQL Learning Note (iv) GROUP by and having usage

Note: The field after select must either be included in group by or be included in the aggregate function after having a. 1. Group BY is a group query, and the general group by ISuse with aggregate functions

Group by has a principle that all columns following the Select Do not have columns that use aggregate functions and must appear behind group by (important)

For example, the following database tables are available:

A B
1 ABC
1 BCD

1 ASDFG

If you have the following query statement ( The statement is wrong, the reason is the preceding principle)

Select A b from table group by A

The intent of the query statement is to get the following results ( of course, just wishful)

A B
Abc
1 BCD

Asdfg

On the right 3 how to become one, so you need to use the aggregate function, as follows (the following is the correct way):

Select A,count (B) as Quantity from table group by A
So the result is
A Quantity
1 3

2. Having

The purpose of the WHERE clause is to remove rows that do not conform to the where condition before grouping the results of the query, that is, to filter the data before grouping, the condition cannot contain a clustering function, and the Where condition is used to display a particular row.

The HAVING clause is used to filter the groups that meet the criteria, that is, to filter the data after grouping, often including clustering functions, to display specific groups using the having condition , or to group by using multiple grouping criteria.

The HAVING clause is restricted to columns and aggregate expressions that are already defined in the SELECT statement. Typically, you need to reference an aggregate value by repeating an aggregate function expression in the HAVING clause, as you did in the SELECT statement. For example:

SELECT A count (b) from TABLE GROUP by A has COUNT (b) >2

MySQL Learning Note (iv) GROUP by and having usage