Preface: SUM(),AVG(),AVG(),AVG(),AVG(),AVG(),AVG(),AVG() HAVING Where and HAVING are both ways of filtering the results of a query. The usage and similarities and differences are explained below. Note: This article uses fields in the EMP table below the default user Scott in the Oracle database, where sal represents the salary of the employee and deptno represents the department number.

First, aggregation function

Aggregation functions, sometimes called statistical functions, are usually used to calculate the maximum, minimum, total, average (MAX,MIN, COUNT, AVG) of a set of data. The fundamental difference between these functions and others is that they typically operate on multiple records. SELECT SUM(sal) FROM emp where sal = SUM(SUM, sal); BY using the GROUP BY clause, you can make the SUM and COUNT functions work on data that belongs to a GROUP.

Where clause

Where pretends to be used only for the value returned from the FROM clause, and each row of data returned from the FROM clause is filtered by the criteria in the WHERE clause. The where clause allows the comparison operators (>,<,>=,<=,<>,! = |, etc.) and logical operators (and, or, not). Since everyone is familiar with the WHERE clause, I won’t repeat it here.

3. Having a clause

The having clause is usually used with the Order by clause. Because the function of HAVING is to further filter the results of group by statistics. For example: now you need to find the department number where the total department salary is greater than 10000? The first step:

select deptno,sum(sal) from emp group by deptno;
Copy the code

The screening results are as follows:

DEPTNO SUM(SAL)

— — — –

30, 9400

20, 10875

10, 8750

So we can see what we want. But now what if we want a department with a total salary of more than 10,000? So we came up with having to filter group statistics to help us do that. The second step:

select deptno,sum(sal) from emp group by deptno having sum(sal)>10000;
Copy the code

The screening results are as follows:

DEPTNO SUM(SAL)

— — — –

20, 10875

Of course, this result is exactly what we wanted.

4. Let’s take a closer look at the where clause and the having clause.

Aggregate statements (sum,min, Max, AVg,count) take precedence over having clauses in a query. The WHERE clause takes precedence over aggregate statements (sum,min, Max, AVg,count) during the query because it is filtered sentence by sentence. HAVING clauses allows us to filter groups of data into groups. , whereas the WHERE clause filters records before aggregating. For example: Now do we want a department number that is not equal to 10 and whose total salary is greater than 8000? Select the department whose department id is not 10 by using the WHERE clause. Then select the department whose salary is not 10 by using the HAVING clause.

select deptno,sum(sal) from emp 
wheredeptno! ='10' group by deptno
having sum(sal)>8000; 
Copy the code

The screening results are as follows:

DEPTNO SUM(SAL)

— — — –

30, 9400

20, 10875

5. Similarities and differences

They are similar in that they define the search criteria. The difference is that the WHERE clause is for a single filter and the HAVING clause is related to a group rather than a single row. Finally: The best way to understand the having and WHERE clauses is the order in which the basic SELECT statements are processed: The WHERE clause accepts only from the FROM clause, whereas the HAVING clause accepts input from the Group by, WHERE, or FROM clause.