Restricting group results

Introduction to Oracle SQL

Hadrien Lacroix

Content Developer

Back to our example

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?

Introduction to Oracle SQL

Limits of WHERE

  • WHERE can't be used to filter groups
    • Group functions can't be used in WHERE clauses
Introduction to Oracle SQL

Limits of WHERE example

SELECT Composer, AVG(Milliseconds)
FROM Track
GROUP BY Composer
WHERE AVG(Milliseconds) > 200000
syntax error at or near "WHERE"
LINE 4: WHERE AVG(Milliseconds) > 200000
        ^
Introduction to Oracle SQL

HAVING

SELECT Composer, AVG(Milliseconds)
FROM Track
GROUP BY Composer
HAVING AVG(Milliseconds) > 200000
| composer    | avg    |
|-------------|--------|
| George Duke | 274155 |
| Miles Davis | 391146 |
| R. Carless  | 251585 |
| ...         | ...    |
Introduction to Oracle SQL

Restricting group results with HAVING

Having clause flow

Introduction to Oracle SQL

HAVING

SELECT Composer, MAX(Milliseconds)
FROM Track
GROUP BY Composer
HAVING MAX(Milliseconds) > 200000
| composer    | max    |
|-------------|--------|
| George Duke | 274155 |
| Miles Davis | 907520 |
| R. Carless  | 251585 |
| ...         | ...    |

Introduction to Oracle SQL

Another example

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 |
| ...           | ...  |
Introduction to Oracle SQL

Guidelines

  • Different group functions can be used in SELECT andHAVING

  • HAVING always has a grouping function

Order of operations:

  1. Rows are filtered by WHERE and GROUP BY
  2. Group function applied to the groups
  3. The groups are filtered by HAVING then outputted
Introduction to Oracle SQL

Let's practice!

Introduction to Oracle SQL

Preparing Video For Download...