Shulou(Shulou.com)06/01 Report--

"Where" is a constraint declaration that uses Where to constrain the data from the database, Where works before the result is returned, and aggregate functions cannot be used in Where.

"Having" is a filter declaration that filters query results after the query returns a result set, and aggregate functions can be used in Having.

Before we can say the difference, we have to introduce the GROUP BY clause, and before we say the GROUP clause, we have to talk about the "aggregate function", a special function in the SQL language. For example, SUM, COUNT, MAX, AVG and so on. The fundamental difference between these functions and other functions is that they generally act on multiple records.

Such as:

SELECT SUM (population) FROM vv_t_bbc

The SUM here acts on all the population fields that return records, and the result is that the query returns only one result, that is, the total population of all countries.

By using the GROUP BY clause, you can make functions such as SUM and COUNT work on data that belongs to a group. When you specify GROUP BY region, only a set of data belonging to the same region (region) will return a row of values, that is, all fields in the table except region (region) can only be calculated through SUM, COUNT and other aggregate functions to return a value.

Let's talk about "HAVING" and "WHERE" again:

The HAVING clause allows us to filter the data of each group after grouping, and the WHERE clause filters the records before aggregation. that is to say, it acts before the GROUP BY clause and the HAVING clause, while the HAVING clause filters the group records after aggregation.

Let's understand the GROUP BY and HAVING clauses through concrete examples:

SQL instance:

First, display the total population and total area of each region:

SELECT region, SUM (population), SUM (area)

FROM bbc

GROUP BY region

First, the returned records are divided into groups with region, which is the literal meaning of GROUP BY. After grouping, the aggregate function is then used to operate on different fields (one or more records) in each group.

Second, show the total population and total area of each area. only those areas with a population of more than 1000000 are shown.

SELECT region, SUM (population), SUM (area)

FROM bbc

GROUP BY region

HAVING SUM (population) > 1000000

[note] here, we cannot use where to filter more than 1000000 regions, because such a record does not exist in the table.

On the contrary, the HAVING clause allows us to filter the grouped data.

Ps: if you want to sort by the field after sum, you can add: order by sum (population) desc/asc

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.