The HAVING Clause
- HAVING specifies a search condition for a group or an aggregate and typically used in a GROUP BY clause.
When GROUP BY is not used, HAVING behaves like a WHERE clause.
HAVING syntax included in the SELECT statement:
SELECT [ALL | DISTINCT] select_list FROM table/view_list [WHERE search_conditions] [GROUP BY group_by_list] [HAVING having_conditions] [ORDER BY order_by_list ]Steps when a query includes WHERE, GROUP BY, aggregates, and HAVING.
In this example HAVING eliminates those sets that include only one book:MySQL, Oracle, SQL server, PostgreSQL:
- The WHERE clause acts first to find the rows you want.
- The GROUP BY clause divides these rows into groups.
- After the groups are formed, SQL calculates the aggregate values (SUM, MIN, etc.) for each group.
- HAVING checks the results from the rows produced by the grouping to see which ones qualify for a final view..
- At last the ORDER BY comes in action to sort on any expressions.
select publisher, min(price) as "Low Price", max(price) as "High price", count(*) "Numbers" from bookstore group by publisher having count(*) > 1 order by publisher, count(*);The result should be:
publisher Low Price High price Numbers Wrox 29 42 5
© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the
correctness in the pages or source codes.
The risk of using this web-site pages or any program
codes from this website is entirely at the individual user.