oracle pl sql error oracle pl sql error oracle pl sql error
 

SQL: HAVING Clause


The HAVING clause is used in combination with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns.

The syntax for the HAVING clause is:

SELECT column1, column2, ... column_n, aggregate_function (expression) FROM tables WHERE predicates GROUP BY column1, column2, ... column_n HAVING condition1 ... condition_n;

aggregate_function can be a function such as SUM, COUNT, MIN, or MAX.

Example using the SUM function

For example, you could also use the SUM function to return the name of the department and the total sales (in the associated department). The HAVING clause will filter the results so that only departments with sales greater than $1000 will be returned.

SELECT department, SUM(sales) as "Total sales" FROM order_details GROUP BY department HAVING SUM(sales) > 1000;

 

.

 
 

 

 
 

Example using the COUNT function

For example, you could use the COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year. The HAVING clause will filter the results so that only departments with at least 25 employees will be returned.

SELECT department, COUNT(*) as "Number of employees" FROM employees WHERE salary > 25000 GROUP BY department HAVING COUNT(*) > 10;

Example using the MIN function

For example, you could also use the MIN function to return the name of each department and the minimum salary in the department. The HAVING clause will return only those departments where the starting salary is $35,000.

SELECT department, MIN(salary) as "Lowest salary" FROM employees GROUP BY department HAVING MIN(salary) = 35000;

Example using the MAX function

For example, you could also use the MAX function to return the name of each department and the maximum salary in the department. The HAVING clause will return only those departments whose maximum salary is less than $50,000.

SELECT department, MAX(salary) as "Highest salary" FROM employees GROUP BY department HAVING MAX(salary) < 50000;

 
 
 
 
Oracle news Oracle guide
     
 
     
  29-11-2006 Guida Oracle 10g
     
  29-11-2006 Guida Oracle9i
     
  29-11-2006 Create Functions
     
  01-04-2007 Oracle Errors
Delete Delete
Between Between
Check Check
Count Count
Datatypes Datatypes
Delete Oracle Delete Statement
Distinct Distinct
Exists Exists
Functions Functions
Grant revoke Grant Revoke
Group by Oracle Group by
Having Having
Oracle Function in Oracle Function in
Indexes Indexes
Insert Insert
Intersect Intersect
Isnull Is null
Joins Joins
Substr Substr
instr Oracle instr
blob Oracle blob
roles Oracle roles
Substr Substr
like Oracle like
Substr Oracle minus
Substr Oracle to_number
union Oracle union
query Oracle query
Substr Oracle unique
Substr Oracle union all
Substr Oracle alter table
 
Oracle Functions PLSQL  l Consulenza Software Verona  l  Siti Web Verona  l   Exceptions  l  Oracle Errors   l  Oracle Collaborations Suite  l  Products  l  Contact
Oracle errors ora