The Art of PostgreSQL/

...

/

Restrict Selected Groups: Having

Restrict Selected Groups: Having

Learn how to use the having clause and the distinct clause to limit the output.

We'll cover the following...

If we are curious about the reasons why those drivers couldn’t make it to the end of the race, we can also inquire about it.

Press + to interact
\set season 'date ''1978-01-01'''
  select status, count(*)
    from results
         join races using(raceid)
         join status using(statusid)
   where date >= :season
     and date <  :season + interval '1 year'
     and position is null
group by status
  having count(*) >= 10
order by count(*) desc;

The query introduces the having clause. Its purpose is to filter the result set to only those groups that meet the having filtering condition, similar to the where clause as it works for the individual rows selected for the result set.

Note that the having clause is not allowed to reference select output aliases to avoid any ambiguity.

We can see that drivers mostly do not finish a race because they ...