JetSQL » Clauses » HAVING
- Syntax:
-
SELECT fieldlist
FROM table
WHERE criteria
GROUP BY grouping_expression
[HAVING criteria] - fieldlist
- The list of fields that are to be retrieved from the table.
- table
- The name of the table from which the information is being retrieved.
- criteria
- The condition(s) that dictate which rows are to be retrieved.
- grouping_expression
- The parameter that specifies a set of columns on which to group matching data.
The HAVING clause is optional and qualifies a 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.
Examples
Code:
SELECT Product, COUNT(Product) AS Tally FROM Suppliers
GROUP BY Product HAVING COUNT(Product) > 1;
Output:
Product Tally Desktop 25 Laptop 19 Mouse 33 Network Card 3 Software 4 Book 6 Accessory 7
(7 row(s) affected)
Explanation:
This example displays a list of different products, along with their count, but only where there are more than one.
Language(s): MS SQL Server
Code:
SELECT Item, COUNT(Item) AS Tally, MAX(UnitPrice) AS MaxPrice
FROM Products GROUP BY Item
HAVING COUNT(Item) > 1 AND MAX(UnitPrice) < 400;
Output:
Item Tally MaxPrice Intellisense Mouse 20 20.00 Travelstar 30gb Hard Drive 3 100.00
(2 row(s) affected)
Explanation:
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 $400. This is an example of linking multiple expressions together by logical operators such as AND and OR. A HAVING clause can contain up to forty (40) such expressions.
Language(s): MS SQL Server
Code:
SELECT Item, COUNT(Item) AS Tally, MAX(UnitPrice) AS MaxPrice
FROM Products WHERE UnitPrice < 400
GROUP BY Item HAVING COUNT(Item) > 1;
Output:
Item Tally MaxPrice Dell Axim X30 13 379.00 Intellisense Mouse 20 20.00 Travelstar 30gb Hard Drive 3 100.00
(3 row(s) affected)
Explanation:
This query shows how to count only those items that cost less that $400 (there could be others that cost more) and that number more than 1.
Language(s): MS SQL Server
See Also:
