Introduction to Oracle SQL
Hadrien Lacroix
Content Developer
SELECT Composer, AVG(Milliseconds)
FROM Track
WHERE UnitPrice = 0.99
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 |
| ... | ... |
What about filtering after grouping?
WHERE
can't be used to filter groupsWHERE
clausesSELECT Composer, AVG(Milliseconds)
FROM Track
GROUP BY Composer
WHERE AVG(Milliseconds) > 200000
syntax error at or near "WHERE"
LINE 4: WHERE AVG(Milliseconds) > 200000
^
SELECT Composer, AVG(Milliseconds)
FROM Track
GROUP BY Composer
HAVING AVG(Milliseconds) > 200000
| composer | avg |
|-------------|--------|
| George Duke | 274155 |
| Miles Davis | 391146 |
| R. Carless | 251585 |
| ... | ... |
SELECT Composer, MAX(Milliseconds)
FROM Track
GROUP BY Composer
HAVING MAX(Milliseconds) > 200000
| composer | max |
|-------------|--------|
| George Duke | 274155 |
| Miles Davis | 907520 |
| R. Carless | 251585 |
| ... | ... |
SELECT Composer, SUM(UnitPrice)
FROM Track
WHERE GenreId = 1
GROUP BY Composer
HAVING COUNT(*) > 4
| composer | sum |
|---------------|------|
| AC/DC | 7.92 |
| Chris Cornell | 9.90 |
| Eddie Vedder | 8.91 |
| ... | ... |
Different group functions can be used in SELECT
andHAVING
HAVING
always has a grouping function
Order of operations:
WHERE
and GROUP BY
HAVING
then outputtedIntroduction to Oracle SQL