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.