Retrieve Data Using SQL

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.”  ;)

Interactive Console