Having usage in SQL and over usage

ArticleDirectory

• SQL having syntax
• SQL having instance

I encountered a question about SQL having statement usage. The result is not answered. Extract the usage here as a record.

Source: http://www.w3school.com.cn/ SQL/SQL _having.asp

 

Having clause

The having clause is added to SQL because the where keyword cannot be used with the aggregate function.

SQL having syntax

 
Select column_name, aggregate_function (column_name)
From table_name
Where column_name operator Value
Group by column_name
Having aggregate_function (column_name) operator Value
 

SQL having instance

 

We have the following "orders" table:

O_id	Orderdate	Orderprice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/05	700	Bush
4	2008/09/28	300	Bush
5	2008/08/06	2000	Adams
6	2008/07/21	100	Carter

Now, we want to find customers whose total order amount is less than 2000.

We use the following SQL statement:

 
Select customer, sum (orderprice) from orders
Group by customer
Having sum (orderprice) <2000
 

The result set is similar:

Customer	Sum (orderprice)
Carter	1700

Now we want to find the customer "Bush" or "Adams" with more than 1500 of the total order amount.

We add a common where clause to the SQL statement:

 
Select customer, sum (orderprice) from orders
Where customer = 'Bush 'or customer = 'adams'
Group by customer
Having sum (orderprice)> 1500
 

Result set:

Customer	Sum (orderprice)
Bush	2000
Adams	2000

 

 

Note the following:

When the WHERE clause, group by clause, having clause, and aggregate function are included at the same time, the execution sequence is as follows:

Execute the WHERE clause to search for qualified data;

Use the group by clause to group data. Run the aggregate function on the group formed by the Group by clause to calculate the values of each group;

Finally, remove the non-conforming group with the having clause.

Select DNO, count (*)
From employee
Group by DNO
Having count (*)> 3
Note: Each element in the having clause must also appear in the select list. Some database exceptions, such as oracle.

Both the having clause and the where clause can be used to set restrictions so that the query results meet certain conditions.

The having Clause limits groups rather than rows. Clustering functions cannot be used in the WHERE clause, but can be used in the having clause.

Select DNO, sum (salary)
From employee
Group by DNO
Having sum (salary)> 10000

 

I am not sure about the over usage. Also, I can copy the following for future reference:

first of all, I think it is strange that the keyword "over" is not mentioned in the w3school SQL tutorial, or even in the index. So I think what is the header of this "over" keyword, is it so mysterious that it is only a special syntax feature of a dbms? I found that it seems to be a special function in Oracle for a long time, so I don't need to worry about it now !!!!!!!