8.3.3 Grouping, Aggregates and Two-Table Queries

8.3.3 Grouping, Aggregates and Two-Table Queries

Some database questions require more than displaying individual tuples. A query may need to form categories, calculate a summary value, or combine related attributes stored in two tables. This section develops those skills using GROUP BY, COUNT, SUM, AVG and INNER JOIN.

The EcoTrack Database

The examples continue the original environmental-monitoring database used in the preceding SQL lessons.

ResearchSite data

Sensor data

Forming Groups with GROUP BY

GROUP BY places tuples that share the same value into the same category. It does not sort the output; its purpose is to define the groups used by the query.

SELECT SensorType
FROM Sensor
GROUP BY SensorType;

The seven sensor tuples form four groups:

Calculating Summaries with Aggregate Functions

An aggregate function processes several tuples and returns a single summary value.

COUNT

SELECT COUNT(*)
FROM Sensor;

This returns 7, because the table contains seven tuples.

SUM

SELECT SUM(SampleInterval)
FROM Sensor;

This returns 150, the total of the seven numeric interval values.

AVG

SELECT AVG(SampleInterval)
FROM Sensor;

This returns the mean sampling interval, approximately 21.4 minutes. The exact displayed precision depends on the DBMS.

One Aggregate Result for Each Group

Without GROUP BY, an aggregate is calculated over the whole qualifying result. With GROUP BY, the function is calculated separately for each group.

SELECT SiteID, COUNT(*)
FROM Sensor
GROUP BY SiteID
ORDER BY SiteID;

A grouped average follows the same structure:

SELECT SiteID, AVG(SampleInterval)
FROM Sensor
GROUP BY SiteID
ORDER BY SiteID;

Valid grouped SELECT lists

In a grouped query, an attribute in the SELECT list should normally be:

• the attribute named after GROUP BY; or
• inside an aggregate function.

SELECT SiteID, COUNT(*)
FROM Sensor
GROUP BY SiteID;

SELECT SiteID, SensorID, COUNT(*)
FROM Sensor
GROUP BY SiteID;

Clause Order in an Analytical Query

A grouped query can also filter source tuples and sort the completed summary.

SELECT SiteID, AVG(SampleInterval)
FROM Sensor
WHERE SampleInterval <= 30
GROUP BY SiteID
ORDER BY SiteID;

Why Query Two Tables?

A normalised database stores different kinds of facts in separate tables. Sensor stores the site identifier, but the readable site name is in ResearchSite. To display both sensor and site details, the DBMS must match related tuples.

Matching Related Tuples with INNER JOIN

An INNER JOIN returns combinations for which the join condition is true. In this database, Sensor.SiteID is matched with ResearchSite.SiteID.

SELECT ResearchSite.SiteName,
       Sensor.SensorID,
       Sensor.SensorType
FROM ResearchSite
INNER JOIN Sensor
ON ResearchSite.SiteID = Sensor.SiteID
ORDER BY ResearchSite.SiteName;

Reading the statement

Filtering a joined result

SELECT ResearchSite.SiteName,
       Sensor.SensorID,
       Sensor.SensorType
FROM ResearchSite
INNER JOIN Sensor
ON ResearchSite.SiteID = Sensor.SiteID
WHERE ResearchSite.RegionCode = 'NW'
ORDER BY ResearchSite.SiteName;

The join is formed using SiteID, then WHERE keeps only rows from sites in the NW region.

Worked Example: Summary Information with Site Names

Step 1: Identify why two tables are needed

SiteName is stored in ResearchSite, while each sensor tuple is stored in Sensor.

Step 2: Identify the join

Match ResearchSite.SiteID with Sensor.SiteID.

Step 3: Identify the groups

The request needs one result per site, so group by ResearchSite.SiteName.

Step 4: Choose the aggregate

Use COUNT(*) to count the joined sensor rows in each site group.

SELECT ResearchSite.SiteName, COUNT(*)
FROM ResearchSite
INNER JOIN Sensor
ON ResearchSite.SiteID = Sensor.SiteID
GROUP BY ResearchSite.SiteName
ORDER BY ResearchSite.SiteName;

Interactive: SQL Analysis Lab

Explore whole-table aggregates, grouped summaries and the process used to match two related tables.

-- Build an aggregate query

Common Mistakes and Misconceptions

Practice

Review