This time we will look at some queries that help manage the numeric data covered in the last tutorial. We will use AVG/MIN/MAX to see the average, minimum, and maximum values.

🆕 🌟
📌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 film length average in the 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' column in payment table
SELECT MIN(amount) AS Minimum, MAX(amount) AS Maximum FROM payment;

# AVG

Q1. Finding the film length average 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 write AVG after SELECT and the column name inside the parentheses. 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 the 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, two 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 last tutorial: (payment_id, customer_id, staff_id, rental_id, amount, payment_date, last_update)

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

Here you’ll see the values 2.99, 0.99 and 5.99 in the amount column. Let’s use MIN and MAX to find the minimum and maximum values. Write MIN or MAX after SELECT and the column name inside the parentheses. 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~

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;