Aggregations - summarizing data

Data-Driven Decision Making in SQL

Bart Baesens

Professor Data Science and Analytics

Overview aggregations

SELECT AVG(renting_price)
FROM movies;
Data-Driven Decision Making in SQL

Overview aggregations

SELECT AVG(renting_price)
FROM movies;

Some aggregate functions in SQL

  • AVG()
  • SUM()
  • COUNT()
  • MIN()
  • MAX()
Data-Driven Decision Making in SQL

Aggregation with NULL values

SELECT COUNT(*)
FROM actors;
  • Result: 145
SELECT COUNT(name)
FROM actors;
  • Result: 145
SELECT COUNT(year_of_birth)
FROM actors;
  • Result: 143
Data-Driven Decision Making in SQL

DISTINCT

SELECT DISTINCT country
FROM customers;

| country       |
|---------------|
| Spain         | 
| Great Britain | 
| Austria       | 
| Poland        | 
| ............. |

SELECT COUNT(DISTINCT country)
FROM customers;
  • Result: 11
Data-Driven Decision Making in SQL

DISTINCT with `NULL` values

SELECT DISTINCT rating
FROM renting
ORDER BY rating;

| rating |
|--------|
| 1      | 
| ...... | 
| 10     | 
| null   |
Data-Driven Decision Making in SQL

Give an alias to column names

SELECT AVG(renting_price) AS average_price, 
         COUNT(DISTINCT genre) AS number_genres
FROM movies;

| average_price | number_genres |
|---------------|---------------|
| 2.21          | 8             |
  • Helps to understand the results when column names are self-explaining.
Data-Driven Decision Making in SQL

Let's practice!

Data-Driven Decision Making in SQL

Preparing Video For Download...