Aggregation and Grouping
Finding Unique Values with DISTINCT
In some instances, we may want to eliminate duplicates from our query results. The DISTINCT keyword, which appears after SELECT, suppresses duplicates, so that each result appears at most once.
Remember the UnitPrice field from the Track table? We already know that most songs have a price of $0.99, so that value appears over and over again in this SELECT statement.
SELECT UnitPrice
FROM Track
LIMIT 5;| UnitPrice |
|---|
| 0.99 |
| 0.99 |
| 0.99 |
| 0.99 |
| 0.99 |
By using the DISTINCT keyword, we can see that the thousands of tracks have only two distinct prices: $.99 and $1.99.
SELECT DISTINCT UnitPrice
FROM Track
LIMIT 5;| UnitPrice |
|---|
| 0.99 |
| 1.99 |
The DISTINCT keyword works on combinations of values (also known as tuples) as well. Let’s say you’re interested in the the city, state locations of your customers in Massachusetts and California. Remember, because we want rows where either condition is true, we need to use OR in the WHERE clause.
SELECT DISTINCT BillingCity, BillingState
FROM Invoice
WHERE (BillingState = 'MA') OR (BillingState = 'CA')
LIMIT 5;| BillingCity | BillingState |
|---|---|
| Boston | MA |
| Mountain View | CA |
| Cupertino | CA |
| Cupertino | CA |
| Boston | MA |
Adding the DISTINCT keyword limits the results such that each combination of BillingCity and BillingState appears at most once.
SELECT BillingCity, BillingState
FROM Invoice
WHERE (BillingState = 'MA') OR (BillingState = 'CA');| BillingCity | BillingState |
|---|---|
| Boston | MA |
| Mountain View | CA |
| Cupertino | CA |
SELECT DISTINCT does not exclude NULL values if they appear in your result set.
SELECT DISTINCT Company
FROM Customer;| Company |
|---|
| Embraer - Empresa Brasileira de Aeronáutica S.A. |
| null |
| JetBrains s.r.o. |
| Woodstock Discos |
| Banco do Brasil S.A. |
| Riotur |
| Telus |
| Rogers Canada |
| Google Inc. |
| Microsoft Corporation |
| Apple Inc. |
Quiz: Distinct Names that Begin With M
Write a query to find the unique FirstName values in the Customer table that begin with the letter M, then order them in alphabetical order.
To write this query, you will need to use a wildcard in the WHERE clause to filter to first names that begin with M and DISTINCT to filter out names that appear more than once.
SELECT DISTINCT FirstName
FROM Customer
WHERE FirstName LIKE 'M%'
ORDER BY FirstName;| FirstName |
|---|
| Mark |
| Michelle |
| Martha |
| Madalena |
| Marc |
| Manoj |
The DISTINCT keyword can also be used inside aggregation functions in the SELECT clause. For example, if we want to identify the unique CustomerId values from California in the Invoice table we would use the following query.
SELECT DISTINCT CustomerId
FROM Invoice
WHERE (BillingState = 'CA');| CustomerId |
|---|
| 16 |
| 19 |
| 20 |
To count these values, wrap DISTINCT CustomerId in COUNT().
SELECT COUNT(DISTINCT CustomerId)
FROM Invoice
WHERE (BillingState = 'CA');| COUNT(DISTINCT CustomerId) |
|---|
| 3 |
Grouping By Value with GROUP BY
In many cases, we don’t just want to find the SUM() or MAX() of a field across all rows. Many datasets are most informative when we think about their measurements as aggregated within discrete groups. For example, we might want to analyze revenue by BillingCountry or the number of tracks by Genre.
Grouping gives us the power to independently apply functions to groups of rows within a table, with each group sharing a discrete value of a particular (typically categorical) variable. No row can appear in more than one group!
Take the Company field in the Customer table.
SELECT DISTINCT Company
FROM Customer;| Company |
|---|
| Embraer - Empresa Brasileira de Aeronáutica S.A. |
| null |
| JetBrains s.r.o. |
| Woodstock Discos |
| Banco do Brasil S.A. |
| Riotur |
| Telus |
| Rogers Canada |
| Google Inc. |
| Microsoft Corporation |
| Apple Inc. |
There are eleven possible values (including NULL) for this column in the Customer table. Let’s say we want to know how many customers (rows) are affiliated with each company.
We can use the COUNT(*) function in combination with GROUP BY to find this.
SELECT Company, COUNT(*)
FROM Customer
GROUP BY Company;| Company | COUNT(*) |
|---|---|
| null | 49 |
| Apple Inc. | 1 |
| Banco do Brasil S.A. | 1 |
| Embraer - Empresa Brasileira de Aeronáutica S.A. | 1 |
| Google Inc. | 1 |
| JetBrains s.r.o. | 1 |
| Microsoft Corporation | 1 |
| Riotur | 1 |
| Rogers Canada | 1 |
| Telus | 1 |
| Woodstock Discos | 1 |
If there are NULL values in the column used with GROUP BY (and no WHERE clause is applied to filter them out) then a group will be created for the rows with NULL values.
Remember our query to find the single biggest purchase in the Invoice table? We can refine that with GROUP BY and SUM() to find the five highest spending customers (and their total spending) across multiple invoices.
SELECT CustomerId, SUM(Total) as TotalSpending
FROM Invoice
GROUP BY CustomerId
ORDER BY TotalSpending DESC
LIMIT 5;| CustomerId | TotalSpending |
|---|---|
| 6 | 49.62 |
| 26 | 47.62 |
| 57 | 46.62 |
| 45 | 45.62 |
| 46 | 45.62 |
Best Practices for GROUP BY
Grouping is powerful and convenient, but there are some nuances to be aware of when using GROUP BY.
GROUP BYclauses have lower priority thanWHEREclauses in terms of execution. This means rows are filtered before they are grouped. However,GROUP BYclauses grammatically followWHEREwhen writing them out.- While it is not required from a syntactic perspective, it is best practice to list the field in the
GROUP BYclause first in theSELECTclause.
Quiz: Basic Grouping
Write a query that displays the AlbumId and number of tracks for each album in the Track table with an AlbumId less than 5. Use NumTracks as an alias for the number of tracks, and then order by NumTracks in ascending order.
The key to this query is to use COUNT(*) in conjunction with GROUP BY to count the number of tracks (rows) per album.
SELECT AlbumId, COUNT(*) as NumTracks
FROM Track
WHERE AlbumId < 5
GROUP BY AlbumId
ORDER BY NumTracks; | AlbumId | NumTracks |
|---|---|
| 2 | 1 |
| 3 | 3 |
| 4 | 8 |
| 1 | 10 |
Grouping Multiple Columns
GROUP BY can be extended to multiple fields or attributes. If there are multiple grouped fields, rows with the same value for all columns in the GROUP BY clause share a group.
SELECT BillingCity, BillingState, SUM(Total) as Revenue
FROM Invoice
WHERE (BillingState = 'CA') or (BillingState = 'MA')
GROUP BY BillingCity, BillingState| BillingCity | BillingState | Revenue |
|---|---|---|
| Boston | MA | 37.62 |
| Cupertino | CA | 38.62 |
| Mountain View | CA | 77.24 |
If no aggregation function is specified for a column in a query with GROUP BY, the query may return an arbitrary value that occurs within that column. It may default to the first row within that group, but there is no guarantee about order.
SELECT GenreId, Name
FROM Track
GROUP BY GenreId
LIMIT 5;| GenreId | Name |
|---|---|
| 1 | For Those About To Rock (We Salute You) |
| 2 | Desafinado |
| 3 | Enter Sandman |
| 4 | Your Time Has Come |
| 5 | Money |
In the example above, Enter Sandman is the Name of a Track with a GenreId of 5, but it’s neither the first in alphabetical order, nor the last.
The best way to avoid this ambiguity is to make sure that, when using GROUP BY, make sure every field in a SELECT clause is either aggregated or included in the GROUP BY.
Quiz: Debugging GROUP BY
A colleague wants to total revenue and the number of unique customers associated with each BillingCountry in the Invoice table. They wrote the following query. However, you know for a fact that there is only one CustomerId with a BillingCountry of Argentina in the table, so these results can’t be right. How can this query be refined to give the number of CustomerId values associated with each BillingCountry?
SELECT BillingCountry, SUM(Total) as Revenue, COUNT(CustomerId) as NumCustomers
FROM Invoice
GROUP BY BillingCountry;Hint: What happens if the same customer has multiple rows in the Invoice table in this query?
Unlike the Track table, which has at most one row per track, it’s possible for the CustomerId to be associated with many purchases in the Invoice table. This means, in order to count the number of unique customers, rather than the number of non-NULL values for the field, you must use COUNT(DISTINCT CustomerID) instead.
SELECT BillingCountry, SUM(Total) as Revenue, COUNT(DISTINCT CustomerId) as NumCustomers
FROM Invoice
GROUP BY BillingCountry;The HAVING Clause
Having fixed the query above, we can see that several BillingCountry values in the database are associated with only one CustomerId. You decide to adjust this query to include only groups in which COUNT(DISTINCT CustomerId) as NumCustomers is > 1. However, this cannot be done with a WHERE clause.
Remember how the WHERE clause is always applied before GROUP BY? How do we filter our groups then? This is where the HAVING clause comes in. The HAVING clause can be used to filter aggregated data, because it is applied after GROUP BY but before ORDER BY and LIMIT.
SELECT BillingCountry, SUM(Total) as Revenue, COUNT(DISTINCT CustomerId) as NumCustomers
FROM Invoice
GROUP BY BillingCountry
HAVING NumCustomers > 1;| BillingCountry | Revenue | NumCustomers |
|---|---|---|
| Brazil | 190.1 | 5 |
| Canada | 303.96 | 8 |
| Czech Republic | 90.24 | 2 |
| France | 195.1 | 5 |
| Germany | 156.48 | 4 |
| India | 75.26 | 2 |
| Portugal | 77.24 | 2 |
| USA | 523.06 | 13 |
| United Kingdom | 112.86 | 3 |
Using HAVING without in a query without GROUP BY generates a syntax error.
SELECT AlbumId, TrackId
FROM Track
HAVING COUNT(TrackId) > 5;HAVING clause on a non-aggregate query
The HAVING clause can be used in conjunction with WHERE. The following query computes the price of each AlbumId composed by Miles Davis by summing the UnitPrice of each Track in the album using a GROUP BY clause. The HAVING clause then restricts the aggregated data to groups (AlbumId values) with an AlbumPrice greater than 10.
SELECT AlbumId, Composer, SUM(UnitPrice) as AlbumPrice
FROM Track
WHERE Composer LIKE '%Miles Davis%'
GROUP BY AlbumId, Composer
HAVING AlbumPrice > 10;| AlbumId | Composer | AlbumPrice |
|---|---|---|
| 48 | Miles Davis | 12.87 |
Aggregated fields that do not appear in the SELECT clause may appear in the HAVING clause, but keep in mind that this filter for each group.
For example, this query lists the sum of the Total associated with each BillingCountry in Invoice, but it is filtered to groups with greater than 4 distinct CustomerId values.
SELECT BillingCountry, SUM(Total)
FROM Invoice
GROUP BY BillingCountry
HAVING COUNT(DISTINCT CustomerId) > 4;| BillingCountry | SUM(Total) |
|---|---|
| Brazil | 190.1 |
| Canada | 303.96 |
| France | 195.1 |
| USA | 523.06 |
Complex HAVING Clauses
The HAVING clause supports the same logical operations as the WHERE clause. For example, the query below computes the revenue and number of unique customers per BillingCountry in Invoice, then filters those grouped results to countries where Revenue > 100 and NumCustomers > 1.
SELECT BillingCountry, SUM(Total) as Revenue, COUNT(DISTINCT CustomerId) as NumCustomers
FROM Invoice
GROUP BY BillingCountry
HAVING (Revenue > 100) AND (NumCustomers > 1); | BillingCountry | Revenue | NumCustomers |
|---|---|---|
| Brazil | 190.1 | 5 |
| Canada | 303.96 | 8 |
| France | 195.1 | 5 |
| Germany | 156.48 | 4 |
| USA | 523.06 | 13 |
| United Kingdom | 112.86 | 3 |
When in doubt, remember that the HAVING clause is for filtering groups and the WHERE clause is for filtering rows.