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.
    1. The WHERE clause acts first to find the rows you want.
    2. The GROUP BY clause divides these rows into groups.
    3. After the groups are formed, SQL calculates the aggregate values (SUM, MIN, etc.) for each group.
    4. HAVING checks the results from the rows produced by the grouping to see which ones qualify for a final view..
    5. At last the ORDER BY comes in action to sort on any expressions.
    In this example HAVING eliminates those sets that include only one book:
    MySQL, Oracle, SQL server, PostgreSQL:
    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.