SQL HAVING.

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