Summarizing data

Intermediate SQL with AI

Jasmin Ludolf

Senior Data Science Content Developer

Aggregate functions

SUM()
AVG()
MIN()
MAX()

What is the average number of votes per film?

Report with summary statistics

Summarizing fields

Aggregate functions summarize multiple rows into a single value

Understand data as a whole or group

Prompt: Show me the total number of votes across films

SELECT SUM(num_votes) AS total_votes
FROM reviews;

|total_votes|
|-----------|
|419507814  |

Calculating field averages

Prompt: What is the average number of votes per film?

SELECT AVG(num_votes) AS average_votes_per_film
FROM reviews;

|average_votes_per_film|
|----------------------|
|84441.991545893720    |

Rounding results

Prompt: What is the rounded average number of votes per film?

SELECT ROUND(AVG(num_votes)) AS avg_votes
FROM reviews;

|avg_votes|
|---------|
|84442    |

ROUND(AVG(num_votes), 1)

84442.0

ROUND(AVG(num_votes), 2)

84441.99

ROUND(AVG(num_votes), -2)

Rounding results

Prompt: ...rounded to one decimal place

SELECT ROUND(AVG(num_votes), 1) AS avg_votes
FROM reviews;

|avg_votes|
|---------|
|84442.0  |

Prompt: ...rounded to the nearest hundred

SELECT ROUND(AVG(num_votes), -2) AS avg_votes
FROM reviews;

|avg_votes|
|---------|
|84400    |

Grouped averages

Prompt: Show me the average rounded budget per language, sorted by highest budget first

SELECT language, ROUND(AVG(budget)) AS average_budget
FROM films
GROUP BY language
ORDER BY average_budget DESC;

|language |average_budget|
|---------|--------------|
|Greek    |              |
|Kannada  |              |    
|Polish   |              |
|Korean   |2741550000    |
...

Minimum and maximum

Prompt: Show each country's lowest and highest budgets, ordered alphabetically

SELECT country, 
       MIN(budget) AS lowest_budget, 
       MAX(budget) AS highest_budget
FROM films
GROUP BY country
ORDER BY country;

|country    |lowest_budget|highest_budget|
|-----------|-------------|--------------|
|Afghanistan|46000        |46000         |
|Argentina  |800000       |2000000       |
|Aruba      |35000000     |35000000      |
...

Identify ranges
Understand scope of data
Understand typical value

Aggregate functions and data types

Numerical fields only

SUM()
AVG()

Various data types

MIN()
MAX()
COUNT()

Minimum <-> Maximum

Lowest <-> Highest
A <-> Z
Earliest <-> Most recent

Minimum and maximum

SELECT MIN(language) AS min_language
FROM films;

|min_language|
|------------|
|Aboriginal  |

SELECT MAX(language) AS max_language
FROM films;

|max_language|
|------------|
|Zulu        |

SQL best practices: aliases

SELECT SUM(budget) AS total_budget
...

SELECT AVG(budget) AS average_budget
...

SELECT country, 
       MIN(budget) AS lowest_budget, 
       MAX(budget) AS highest_budget
...

SELECT AVG(num_user), AVG(num_critic)
FROM reviews;

|avg     |avg     |
|275.6...|141.9...|

SQL best practices: formatting

Write queries on multiple lines
Use indentation

SELECT country, 
       MIN(budget) AS lowest_budget, 
       MAX(budget) AS highest_budget
FROM films
GROUP BY country
ORDER BY country;

Glasses on paper

SQL icon

Ready to analyze?

Intermediate SQL with AI