Creating groups of data
Introduction to Oracle SQL
Hadrien Lacroix
Content Developer
Grouping data
Group information
| Composer | Milliseconds |
|-----------------|--------------|
| Antonio Vivaldi | 199,086 |
| Pearl Jam | 122,801 |
| Pearl Jam | 65,593 |
| Jimmy Page | 401,920 |
| Jimmy Page | 386,063 |
| Jimmy Page | 132,702 |
| Jimmy Page | 189,675 |
| Jimmy Page | 126,641 |
| Carlos Santana | 318,432 |
| Carlos Santana | 296,437 |
| Carlos Santana | 882,834 |
| ... | ... |
What is the average track length of songs written by each composer?
GROUP BY- divide the rows in a table into groups
- use group functions to get summary information for each group
GROUP BY
SELECT Composer, AVG(Milliseconds)
FROM Track
GROUP BY Composer
| Composer | AVG(Milliseconds) |
|-----------------|-------------------|
| Antonio Vivaldi | 199,086.0 |
| Pearl Jam | 94,197.0 |
| Jimmy Page | 474,888.3 |
| Carlos Santana | 499,234.3 |
| ... | ... |
GROUP BY and WHERE
SELECT Composer, AVG(Milliseconds)
FROM Track
WHERE Genre = 1
GROUP BY Composer
| Composer | AVG(Milliseconds) |
|-----------------|-------------------|
| Antonio Vivaldi | 199,086.0 |
| Pearl Jam | 94,197.0 |
| Jimmy Page | 474,888.3 |
| Carlos Santana | 499,234.3 |
| ... | ... |
GROUP BY and ORDER BY
SELECT Composer, AVG(Milliseconds) AS Average
FROM Track
GROUP BY Composer
ORDER BY AVG(Milliseconds)
| Composer | AVG(Milliseconds) |
|-----------------|-------------------|
| Pearl Jam | 94,197.0 |
| Antonio Vivaldi | 199,086.0 |
| Jimmy Page | 474,888.3 |
| Carlos Santana | 499,234.3 |
| ... | ... |
GROUP BY and ORDER BY
SELECT Composer, AVG(Milliseconds) AS Average
FROM Track
GROUP BY Composer
ORDER BY 2
| Composer | AVG(Milliseconds) |
|-----------------|-------------------|
| Pearl Jam | 94,197.0 |
| Antonio Vivaldi | 199,086.0 |
| Jimmy Page | 474,888.3 |
| Carlos Santana | 499,234.3 |
| ... | ... |
GROUP BY and ORDER BY
SELECT Composer, AVG(Milliseconds) AS Average
FROM Track
GROUP BY Composer
ORDER BY Average
| Composer | Average |
|-----------------|-------------------|
| Pearl Jam | 94,197.0 |
| Antonio Vivaldi | 199,086.0 |
| Jimmy Page | 474,888.3 |
| Carlos Santana | 499,234.3 |
| ... | ... |
Guidelines
Any column or expression in the SELECT statement that is not an aggregate function must be in the GROUP BY clause
SELECT Composer, AVG(Milliseconds), UnitPrice
FROM Track
GROUP BY Composer
column "track.unitprice" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2: SELECT Composer, AVG(Milliseconds), UnitPrice
Guidelines
Any column or expression in the SELECT list that is not an aggregate function must be in the GROUP BY clause
SELECT Composer, AVG(Milliseconds), MAX(UnitPrice)
FROM Track
GROUP BY Composer
| Composer | AVG(Milliseconds) | MAX(UnitPrice) |
|-----------------|-------------------|----------------|
| Antonio Vivaldi | 199,086.0 | 0.99 |
| Pearl Jam | 94,197.0 | 0.99 |
| Jimmy Page | 474,888.3 | 0.99 |
| Carlos Santana | 499,234.3 | 0.99 |
| ... | ... | ... |
Guidelines
Expressions that are specified in the GROUP BY do not have to be included in the SELECT statement
SELECT AVG(Milliseconds)
FROM Track
GROUP BY Composer
| AVG(Milliseconds) |
|-------------------|
| 199,086.0 |
| 94,197.0 |
| 474,888.3 |
| 499,234.3 |
| ... |
Multiple columns
SELECT Country, City, COUNT(CustomerId)
FROM Customer
GROUP BY Country, City
| Country | City | COUNT(CustomerId) |
|-----------|---------------------|-------------------|
| Argentina | Buenos Aires | 1 |
| Australia | Sidney | 1 |
| Austria | Vienne | 1 |
| Belgium | Brussels | 1 |
| Brazil | Brasilia | 1 |
| Brazil | São José dos Campos | 1 |
| Brazil | São Paulo | 2 |
| ... | ... | ... |
Let's practice!
Introduction to Oracle SQL
