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
SQL Min and Max Aggregation Functions with Partition By Clause In this SQL tutorial for SQL Server database developers, I want to show how SQL Max and Min aggregate functions are used with Partition By clause in a Transact-SQL query.
Sql function max min. Follow asked Aug 13 '13 at 10:14. Aung Kaung Hein Aung Kaung Hein. 1,448 5 5 gold badges 22 22 silver badges 40 40 bronze badges. Summary: in this tutorial, you will learn how to use the SQL MIN function to get the minimum value in a set. Introduction to SQL MIN function. The MIN function returns the minimum value in a set of values. The MIN function ignores the NULL values. The following is the syntax of the MIN function. The third class of functions in SQL Server are Aggregate-Valued Functions. The MIN and MAX SQL Server functions are Aggregate-Valued Functions. Window Functions are a relatively new class. They perform calculations like aggregate functions but do it over a set of rows related to the current row.
Query 2. Finding the minimum/maximum value of 'amount' column in payment table
# 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:
💡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:
💡 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:
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:
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!
🗒️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:
- SQL Tutorial
- Advanced SQL
- SQL Useful Resources
- Selected Reading
SQL MAX function is used to find out the record with maximum value among a record set.
To understand MAX function, consider an employee_tbl table, which is having the following records −
Min Max In Sql
Now suppose based on the above table you want to fetch maximum value of daily_typing_pages, then you can do so simply using the following command −
Min Max Function In Sql
You can find all the records with maxmimum value for each name using GROUP BY clause as follows −
You can use MIN Function along with MAX function to find out minimum value as well. Try out the following example −