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 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} ;
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 (
name VARCHAR(33),
country VARCHAR(20),
stores DECIMAL
);
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.)
name | country | stores |
Burger Barn | CN | 2391 |
Burger Barn | JP | 2975 |
Burger Barn | US | 14146 |
Coffee Creek | CN | 4704 |
Coffee Creek | JP | 1464 |
Coffee Creek | US | 8941 |
Pizza Palace | CN | 4563 |
Pizza Palace | JP | 1181 |
Pizza Palace | US | 4020 |
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:
franchise | locations |
Burger Barn | 19512 |
Coffee Creek | 15109 |
Pizza Palace | 9764 |
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 ;
mega-franchise | locations |
Burger Barn | 19512 |
Coffee Creek | 15109 |
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
To learn more about SQL, check out other SQL blog posts and enroll in our SQL Nanodegree program.
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 (
name VARCHAR(33),
country VARCHAR(20),
stores DECIMAL
);
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 );
Announcing iOS Development with SwiftUI and SwiftData The demand for iOS developers proficient in Swift…
In a world driven by technology, finding the right opportunity to break into the tech…
This month, more than 1,000 people received notifications of their free enrollment in the AI…
Developers & IT Pros are harnessing the power of Google Cloud to solve real-world problems.…
From Weekend Projects to Web3's Next Big Thing Ready to help shape the future of…
It might be obvious by now, but here at Udacity, we really love tech and…