Home page
 
 Home 
 ASP 
 PHP 
 SQL 
 HTML 
 JavaScript 
 Search 
 Contact 
 
Search
or browse popular tags
Subscription

Sign up for the free email newsletter for new tips, tutorials and more. Enter your email address below, and then click the button.

Privacy Policy

RSS Twitter

SQL: HAVING Clause

Print

The Having clause is optional and used in combination with the Group By clause. It is similar to the Where clause, but the Having clause establishes restrictions that determine which records are displayed after they have been grouped. It is typically placed near the end of the SQL statement, and a SQL statement with the Having clause may or may not include the Group By clause.

The syntax for Having is as follows:

SELECT column1, ... column_n, aggregate_function (expression)
FROM table_name
[WHERE condition]
[GROUP BY column1, ... column_n]
HAVING condition

Example #1

Select Item, Sum(Price) as TotalSum
From Antiques
Group by Item
Having Sum(Price)>57

This query returns a list of different items and a field called 'TotalSum' that counts the result sum of antiques that supply each item. The Having clause will return only those items with result sum greater than 57 dollars.  

Example #2

Select SellerID, Count(*) as Number_of_Sellers
From Antiques
Where BuyerID=21
Group by SellerID
Having Count(*)>1

This example displays a list of seller's ids, along with their count, but only where there are more than one seller with BuyerID equal to 15.

Example #3

SELECT Item, COUNT(Item) AS Total, MAX(Price) AS MaxPrice
FROM Antiques
GROUP BY Item
HAVING COUNT(Item) > 1 AND MAX(Price) < 500

This example returns a list of items only where the whole group meets the criteria of the Having clause, that is only items of which there are more than 1, and none of which cost more than $500. This is an example of linking multiple expressions together by logical operators such as AND and OR. A Having clause can contain up to 40 such expressions.


If you'd like to see how it works, enter SQL statement and press Execute
 




Tags:

Add To: Add to dzone dzone | Digg this digg | Add to del.icio.us del.icio.us | Stumble it stumbleupon

  • Comments





Copyright © 2005-2014             www.WebCheatSheet.com All Rights Reserved.