Skip to main content

SQL HAVING Clause

Body

The HAVING Clause is always used after the GROUP BY clause, it can not come without the GROUP BY clause. It works the same as the WHERE Clause, it is therefore also used to apply conditions on the aggregate_functions which was impossible by using simple WHERE clause.

The HAVING clause simply contains the conditions to filter data just like in WHERE clause.

SQL HAVING Syntax

SELECT column_name(s), aggregate_function(column_name) FROM Table WHERE some_condition GROUP BY grouping_condtion HAVING aggregate_function(column_name) operator value

Consider the following table for this exercise

Users

Firstname Lastname Salary DeptID
John Smith 1000 1
Mathew Simon 3000 1
Bill Steve 2200 1
Amanda Rogers 1800 2
Steve Hills 2800 2
Steve jobs 2400 2
bill cosby 700 3

Example # 1

SELECT AVG(Salary) AS Average_Salary, DeptID FROM Users GROUP BY DeptID HAVING AVG(Salary) > 2000

Result of the Query

Average_Salary DeptID
2066.66 1
2333.33 2

The data in the above example is first grouped using the Salary and DeptID column. The result is calculated using the AVG function and since the WHERE clause is not applicable here we use the HAVING Clause. The salary returned is those that meet the criteria which is above average of $2000.

Comments

Submitted byAnonymous (not verified)on Thu, 04/21/2011 - 18:18

I have two table:- 1- ledger id debit credit 2-employee id name adress I want to make a query to total of sum of ebit & sum of credit with join command. please help me technique: vb6 with access I have tried to: sql = "SELECT Sum(debit) AS totdebit, Sum(credit) AS totcredit" & _ "FROM (ledger OUTER JOIN employee ON employee.ID = ledger.ID)" & _ "GROUP BY ledger.ID" my mail id is [email protected]

Add new comment