SQL Having — Finding, Grouping, and Filtering

Relational databases store information in tables — in columns that describe aspects of an item and rows that tie together the columns. Similarly, the SQL Where clause filters rows matching specified criteria, the SQL Having clause filters records returned by the SQL Group By clause; only the groups meeting the criteria will be returned. Please note that:

• The SQL Having clause requires that a SQL Group By clause is present
• Both the SQL Where and SQL Having clauses can be used in one statement
• The SQL Having clause must follow the SQL Group By clause in a query
• The SQL Having clause must precede the SQL Order By clause, if used

The syntax of the SQL Having clause, with all the aforementioned requirements, is:

SELECT
  column(s)
FROM
  table
WHERE
  row criteria
GROUP BY
  column
HAVING
  group criteria
ORDER BY
  column {ASC|DESC} ;

Setting the Table for Grouping Rows

Consider a table of imaginary fast-food franchises — Burger Barn, Coffee Creek, and Pizza Palace — with the total number of stores in each country. (The country values are the ISO-3166 Country Codes.) The SQL that describes this table is:

CREATE TABLE franchises (
nameVARCHAR(33),
countryVARCHAR(20),
storesDECIMAL
);

The SQL to query the sample values is:

SELECT
  name, country, stores
FROM
  franchises
ORDER BY
  name ASC ;

This query returns the following rows. (The SQL to create and populate the sample data tables for these examples appears below.)

Grouping Rows and Summing Up Values

To see the total number of stores in each franchise, we modify the SQL Select statement by asking for the SUM(stores) and also GROUP BY name. The query then becomes:

SELECT
  name AS franchise,
  SUM(stores) AS locations
FROM
  franchises
GROUP BY
  name
ORDER BY
  name ASC ;

The summed information returned is:

Seeing Only Those Groups Which Have Particular Characteristics

Perhaps we’re interested in mega-franchises, those defined as having more than 15,000 locations. Building upon our earlier query, we find the mega-franchises by:

SELECT
  name AS 'mega-franchise',
  SUM(stores) AS locations
FROM
  franchises
GROUP BY
  name
HAVING
  locations > 15000
ORDER BY
  name ASC ;

Conclusion

Covered here is the SQL Having clause, modifying the SQL Group By clause to provide fine-grained control over which aggregated data are displayed.

A use case about worldwide franchises was built, from the ground up, refining the required SQL to ultimately display “mega-franchises” while also demonstrating the particular syntax — the order in which the SQL Having clause, the SQL Group By clause, and the (optional) SQL Order By clause must appear 

Start Learning

To learn more about SQL, check out other SQL blog posts and enroll in our SQL Nanodegree program.

SQL Sample Table Code

The following will build and populate the sample data referred to in this blog post. Test SQL interactively at  SQL Fiddle or ExtendsClass.

CREATE TABLE franchises (
nameVARCHAR(33),
countryVARCHAR(20),
storesDECIMAL
);
INSERT INTO franchises
VALUES
    ( 'Pizza Palace', 'CN', 4563),
    ( 'Pizza Palace', 'JP', 1181),
    ( 'Pizza Palace', 'US', 4020),
    ( 'Burger Barn', 'CN', 2391 ),
    ( 'Burger Barn', 'JP', 2975 ),
    ( 'Burger Barn', 'US', 14146 ),
    ( 'Coffee Creek', 'CN', 4704 ),
    ( 'Coffee Creek', 'JP', 1464 ),
    ( 'Coffee Creek', 'US', 8941 );