Filtering and sorting

In lesson 1, we learned how to filter data using the WHERE clause. Data that is filtered in this way is not aggregated. Filtering takes place beforehand. So if we want to filter on the result of an aggregation, we have to do it somehow differently.

Filtering aggregated data with the HAVING clause

With the keyword HAVING we can introduce a predicate that uses aggregate functions. For example, it compares the summed values of a grouping. If we want to see the IDs of all agents who have more than 100 days of effort, then we can do it this way:

SELECT assignee, 
       SUM(effort) AS effort_total
  FROM tasks
 GROUP BY assignee
 HAVING SUM(effort) > 100

Unfortunately, we cannot refer to the name of the EFFORT_TOTAL column previously defined in the field list. Instead, in our HAVING clause, we need to repeat the aggregate function again. On the other hand, this means that we can also use HAVING to filter for calculated values that do not appear in the field list.

Often we also want to sort the data based on the calculated fields. However, unlike the HAVING and GROUP BY clauses, here we can refer to the column names defined in the field list. So for example: ORDER BY sum_effort DESC . The ORDER BY clause always comes at the end after the grouping and HAVING clauses.