• 20 hours
  • Medium

Free online content available in this course.

course.header.alt.is_video

course.header.alt.is_certifying

Got it!

Last updated on 4/19/21

Improve your aggregations using HAVING

Log in or subscribe for free to enjoy all this course has to offer!

Let’s take our most recent query, which we wrote in the last chapter:

SELECT
i.id AS intermediary_id,
i.name AS intermediary_name,
e.jurisdiction,
e.jurisdiction_description,
count(*) as cnt
FROM
intermediary i,
assoc_inter_entity a,
entity e
WHERE
a.entity = e.id AND
a.inter = i.id AND
(i.id = 5000 OR i.id = 5001)
GROUP BY
i.id, i.name, e.jurisdiction, e.jurisdiction_description
ORDER BY
cnt DESC
LIMIT 5;

Suppose I want to retrieve only rows in which the number of companies is greater than 100, because I think these will be the most important.

The first idea that comes to mind is to apply a restriction. In this case, that would mean adding a condition to the  WHERE  clause.

Unfortunately, however, this is not the right solution, the reason being that  WHERE  clause conditions are applied to the rows before aggregation.

For this reason, if we want to apply a restriction after aggregation, we need a  HAVING  clause.

HAVING Clauses

To find out which jurisdictions are home to intermediaries that have created more than 100 companies, we need to write the following:

SELECT
i.id AS intermediary_id,
i.name AS intermediary_name,
e.jurisdiction,
e.jurisdiction_description,
count(*) as cnt
FROM
intermediary i,
assoc_inter_entity a,
entity e
WHERE
a.entity = e.id AND
a.inter = i.id AND
(i.id = 5000 OR i.id = 5001)
GROUP BY
i.id, i.name, e.jurisdiction, e.jurisdiction_description
HAVING
count(*) > 100 ;

Note that in the  HAVING  clause, it is only natural to use an aggregate function, something that isn’t possible in a  WHERE  clause.

This is not overly complicated; you just need to remember it to avoid mistakes!

Summary

  • WHERE  clause conditions are verified before aggregation.

  • To restrict rows after aggregation, you need to use a  HAVING  clause.

A Little Exercise!

The database is online. Get some practice by rewriting the queries found in this chapter. Don’t hesitate to change them, play with them, and “see what happens.”  ;)

Coding application
Houston... ?
It seems you're not connected to the internet.
You are not signed in

Sign in to access coding exercises and test your new skills.

Make sure to practice in this chapter before moving on.

Example of certificate of achievement
Example of certificate of achievement