GROUP BY - HAVING
You can apply HAVING statement to the field group selection. Its format corresponds to format of WHERE statement.
This statement consists of HAVING keyword which is followed by selection condition.
What is the average order cost for each employee out of the number of ones whose total order cost exceeds $30000?
SELECT rep, AVG(amount) FROM orders GROUP BY rep HAVING SUM(amount) > 30000.0
The result is generated in such a way:
- If the query is the union-type query (UNION, INTERSECT, DIFFERENCE), you should execute the steps 2 - 7 for each part and get the separate results.
- To form the product of the tables listed in FROM statement. If only one table is indicated in FROM statement, it is the product itself.
- If there is WHERE statement, you should apply the selection condition given in it to each record of product table and leave in it the only records for which this condition is executed; that is has TRUE value; you should suppress (clip) the records for which the selection condition has FALSE or NULL value.
- If there is GROUP BY statement you should divide the records left in the product table into groups in such a way that records in every group have the same values in all the fields of clustering.
- If there is HAVING statement you can apply the selection condition set in it to each record group and leave in the product table the only groups for which this condition is fulfilled, that is has TRUE value; you should suppress the groups for which the selection condition has FALSE or NULL value.
- For each of the left records (or for each record group) you should compute the value of each element in the returning fields' list and create one record to the query results' table. With any field-pointer you should take the field value for the current record (or the record group). As a statistic function argument you can apply the field value out of all fields included into group if GROUP BY statement is indicated; in another case you should use the field values out of all records of result table.
- If DISTINCT predicate is indicated you should suppress all the repetition records out of the query results' table.
- If the query is the union-type query (UNION, INTERSECT, DIFFERENCE), you should perform appropriate set-operation for getting single table. You should suppress the repetition records out of this table if ALL keyword isn't indicated.
- If there is ORDER BY statement you should sort the query result.