Group functions

Introduction to Oracle SQL

Hadrien Lacroix

Content Developer

Aggregating data

SELECT SUM(Milliseconds)
FROM Track

| SUM(Milliseconds) |
|-------------------|
| 1,378,778,040     |

SELECT AVG(Milliseconds), MEDIAN(Milliseconds)
FROM Track

| AVG(Milliseconds) | MEDIAN(Milliseconds) |
|-------------------|----------------------|
| 393,599.2         | 255,634              |

SELECT MIN(Milliseconds), MAX(Milliseconds)
FROM Track

| MIN(Milliseconds) | MAX(Milliseconds) |
|-------------------|-------------------|
| 1,071             | 5,286,953         |

-- Number of rows in a table
SELECT COUNT(*)
FROM Track

-- Number of rows with non-null values
SELECT COUNT(Milliseconds)
FROM Track

-- Number of distinct non-null values
SELECT COUNT(DISTINCT Milliseconds)
FROM Track

| COUNT(*) |
|----------|
| 3503     |

| COUNT(Milliseconds) |
|---------------------|
| 3503                |

| COUNT(DISTINCT Milliseconds) |
|------------------------------|
| 3080                         |

SELECT MIN(Milliseconds)
       SUM(Milliseconds)
FROM Track

| MIN(Milliseconds) | SUM(Milliseconds) |
|-------------------|-------------------|
| 1,071             | 1,378,778,040     |

SELECT MIN(Milliseconds) AS minimum
       SUM(Milliseconds) AS "Total Duration"
FROM Track

| MINIMUM           | Total Duration    |
|-------------------|-------------------|
| 1,071             | 1,378,778,040     |

CHAR, VARCHAR2

SELECT Name 
FROM Track

| Name           |
|----------------|
| Ashes To Ashes |
| Layla          |
| Everlong       |
| ...            |

NUMBER

SELECT Milliseconds 
FROM Track

| Milliseconds |
|--------------|
| 343719       |
| 205688       |
| 267728       |
| ...          |

DATE, DATETIME

SELECT BirthDate 
FROM Employee

| BirthDate  |
|------------|
| 1962-02-18 |
| 1958-12-08 |
| 1973-08-29 |
| ...        |

Introduction to Oracle SQL