[SQL Basic] What is the average film length? — SQL AVG/MIN/MAX

#10. Marketing/Data Analysis for Beginners

This time we will look at other queries to manage the numeric data covered in the 9th tutorial. We will use AVG/MIN/MAX to see the average, minimum, and maximum of values.

Here’s a quick review if you need it:

Lesson 3:
📌SELECT: you’re asking the database to show you something
📌FROM: you’re giving the database a location to search
📌WHERE: you’re narrowing down/specifying the location

Lesson 4:
📌ORDER BY: you’re asking for a specific order for the list to be displayed

Lesson 5:
📌AND: find information that matches 2 specific categories
📌OR: find all information that matches at least 1 specified category
📌IN: find information that matches any of the following categories

Lesson 6:
📌LIKE: used to find a specific pattern in a column

Lesson 7:
📌AS: used to rename columns
📌DISTINCT: used to see unique values
📌IS NULL: used to see null values

Lesson 9:
📌COUNT: find the number of rows that matches specified criteria
📌SUM: find the total sum of a numeric column

🆕 🌟
📌AVG: find the average value of a numeric column
📌MAX: find the largest value of the selected column
📌MIN: find the smallest value of the selected column

#Table of Contents

Query 1. Finding the average of film length in film table

SELECT AVG (length) FROM film; SELECT AVG (length) AS average, SUM(length)/COUNT(length) AS ‘sum/count’ FROM film;

Query 2. Finding the minimum/maximum value of amount in payment table

SELECT MIN(amount) AS Minimum, MAX(amount) AS Maximum FROM payment;

# AVG

Q1. Finding the average of film length in the film table

SELECT AVG (column name)

We found the total length of all films in the last tutorial. Now we’re going to find the average value of film length by using AVG. It has the same pattern as COUNT, so you can just write AVG after SELECT and the column name inside the parenthesis. We’re also going to rename the columns to clean things up. Like this:

SELECT AVG(length) AS average FROM film;

💡This will give you the value of 115.2720.

We can also get the average by using the SUM and COUNT queries we learned last time. Since average is a value that divides the total sum by the quantity, we can write our query like this:

SELECT SUM(length)/COUNT(length)AS ‘sum/count’ FROM film;

💡 The same value of 115.2720 will show up!

Let’s take it one step further and put both statements in the same query for comparison:

SELECT AVG(length) AS average, SUM(length)/COUNT(length)AS ‘sum/count’ FROM film;

There we have it, 2 different ways to get the average value!

# MIN/MAX

Q2. Finding the minimum/maximum value of the amount column in the payment table

SELECT MIN (column name) SELECT MAX (column name)

Let’s check the payment table to find the minimum/maximum values.
We are using this table for the first time, so take a look at what’s inside the columns like we did in the 8th tutorial: (payment_id, customer_id, staff_id, rental_id, amount, payment_date, last_update)

🔑 Or you can use SELECT * FROM payment; for a quick look.

Here you’ll see the value of 2.99, 0.99 and 5.99 in the amount column. Let’s use MIN and MAX to find the minimum/maximum value. Write MIN or MAX after SELECT and the column name inside the parenthesis. Like this:

⏬SELECT MIN(amount) FROM payment;
⏫SELECT MAX(amount) FROM payment;

To clean it up and move both values into one table, we can use AS to rename the columns and put minimum and maximum queries in the same query:

SELECT MIN(amount) AS Minimum, MAX(amount) AS Maximum FROM payment;

Now that we have learned how to use AVG, MIN and MAX, let’s try an example question.

# Practice Time

📰 We got an easy one for you this time. Can you find the Average,
Minimum, and Maximum Value of replacement_cost in the Film table?

👍 Try it on your own before you check out the answer below~

🔑You can write AVG, MIN, and MAX after SELECT to see those values of replacement_cost in the film table.

Did you get the values 9.99 for minimum and 29.99 for maximum? How about the average? Scroll down to check the answer we’ve provided!

SELECT AVG (replacement_cost), MIN(replacement_cost), MAX(replacement_cost) FROM film;

It’s always good practice to go back and rename your columns for cleaner results. It will help you in the future to sift through the information faster and easier. Try this one:

SELECT AVG (replacement_cost) AS Average_Cost, MIN(replacement_cost) AS Minimum_Cost, MAX(replacement_cost) AS Maximum_Cost FROM film;

Need some visuals? Follow our video tutorial linked below:

# Tutorial Video

# [SQL Basic Series] — Marketing/Data Analysis for Beginners

1. What is SQL? — Database Introduction
2. Downloading SQLGate and Connecting to a Database
3. What kind of data is in the ‘film’ table? — SELECT/FROM/WHERE
4. How to sort film lists by price (rental rate)? — ORDER BY
5. Can I rent a 3 hour movie for $0.99? — AND/OR/IN
6. How can I find a movie that’s thrilling? — LIKE
7. How to rename columns and see unique values? — AS/DISTINCT/IS NULL
8. Let’s see Sample Database tables!
9. What is the total length of all the films added together? — COUNT/SUM
10. What is the average film length? — AVG/MIN/MAX
11. How many movies have the same rating? — GROUP BY/HAVING
12. Let’s designate a value based on the rental duration. — CASE
13. Let’s find out the movie genres by joining tables! — INNER JOIN
14. Let’s check movie rentals by the inventory list! — LEFT/RIGHT JOIN
15. Let’s find customers that have rented movies priced $9.99! — Subquery
16. How to work with Date Functions in SQL — DATE_FORMAT, DATEDIFF
17. How to work with String Functions in SQL — CONCAT, LENGTH, SUBSTR
18. Let’s use some Numeric SQL functions — TRUNCATE, ROUND, MOD
19. Let’s learn some advanced CASE queries!
20. Let’s learn some advanced JOIN queries!