next up previous contents index
Next: Query Tips Up: SQL Aggregates Previous: Using GROUP BY

  
Using HAVING

One more aggregate capability is often overlooked--the HAVING clause. HAVING allows a user to perform conditional tests on aggregate values. It is often employed in conjunction with GROUP BY. With HAVING, you can include or exclude groups based on the aggregate value for that group. For example, suppose you want to know all states in which you have more than one friend. Looking at the first query in Figure [*], you can see exactly which states have more than one friend. HAVING allows you to test the count column, as shown in Figure [*].  

        test=> SELECT state, COUNT(*)
        test-> FROM friend
        test-> GROUP BY state
        test-> HAVING COUNT(*) > 1
        test-> ORDER BY state;
         state | count 
        -------+-------
         MA    |     2
         NJ    |     2
        (2 rows)
 

Aggregates cannot be used in a WHERE clause; they are valid only inside HAVING. 


Bruce Momjian
2001-05-09