Filtering A Query With a Value in the GROUP BY Clause

The GROUP BY clause used in SQL queries allows you to condense data into a single row that share the same values for those columns for the grouped columns or expressions. It can be very helpful for doing aggregations. Suppose we have the following table named favorite_sports:

 id | name |     sport
----+------+---------------
  1 | Matt | Skiing
  2 | Nate | Rock Climbing
  3 | Dave | Skiing

We can find out how many people like each sport using the following SQL query:

SELECT sport, count(*) AS people
  FROM favorite_sports
 GROUP BY sport;

This produces the following results:

     sport     | people
---------------+--------
 Skiing        |      2
 Rock Climbing |      1

Now suppose we only want to show those sports that have more than one person interested in them. Putting the count(*) function in the WHERE clause will throw an error saying aggregates are not allowed. Instead we must use the HAVING clause and it appears after the GROUP BY clause as in the following example:

SELECT sport, count(*) AS people
  FROM favorite_sports
 GROUP BY sport HAVING count(*) > 1;

Which produces the following results:

  sport | people
--------+--------
 Skiing |      2
Don't be tempted to use the alias in the HAVING clause as you will get an error saying that the alias column does not exist.