SQL Series...38

Subqueries in a HAVING clause

You can have a correlated subquery in a HAVING clause just as you can in a WHERE clause. As I mention in previous article, a HAVING clause is normally pre-ceded by a GROUP BY clause. The HAVING clause acts as a filter to restrict the groups created by the GROUP BY clause. Groups that don’t satisfy the condi-tion of the HAVING clause are not included in the result. When used in this way, the HAVING clause is evaluated for each group created by the GROUP BY clause. In the absence of a GROUP BY clause, the HAVING clause is evaluated for the set of rows passed by the WHERE clause, which is considered to be a single group. If neither a WHERE clause nor a GROUP BY clause is present, the HAVING clause is evaluated for the entire table:

SELECT TM1.EmpID

FROM TRANSMASTER TM1

GROUP BY TM1.EmpID

HAVING MAX (TM1.NetAmount) >= ALL

(SELECT 2 * AVG (TM2.NetAmount)

FROM TRANSMASTER TM2

WHERE TM1.EmpID <> TM2.EmpID) ;

This query uses two aliases for the same table, enabling you to retrieve the EmpID number of all salespeople who had a sale of at least twice the average sale of all the other salespeople. The query works as follows:

1. The outer query groups TRANSMASTER rows by the EmpID. This is done with the SELECT, FROM, and GROUP BY clauses.

2. The HAVING clause filters these groups. For each group, it calculates the MAX of the NetAmount column for the rows in that group.

3. The inner query evaluates twice the average NetAmount from all rows of TRANSMASTER whose EmpID is different from the EmpID of the current group of the outer query. Note that in the last line you need to reference two different EmpID values, so in the FROM clauses of the outer and inner queries, you use different aliases for TRANSMASTER.

4. You then use those aliases in the comparison of the query’s last line to indicate that you’re referencing both the EmpID from the current row of the inner subquery (TM2.EmpID) and the EmpID from the current group of the outer subquery (TM1.EmpID).