SQL Essentials Part Three : SUM, MIN, MAX, and AVG

Continuing our learning into the aggregate functions of SQL, in this blog post we tackle four commonly used but powerful aggregate functions.

In the previous blog post, we were introduced into the aggregate functions of SQL through the use of the COUNT function. As we discussed, an aggregate function is  a function that allows us to take data from a column and perform computations on it to return a single value. To further expand on aggregate functions, we will now look into four more aggregate functions, beggining with the SUM function.

SUM

As it's name suggests, the SUM function takes the values of a column and adds them together to return one total value. Lets try a quick example. Say we need to get the total amount of units on order from our products. This can be done by inserting the column name inside the parenthesis for the sum function.

The query will then return a single value under the name of TotalUnitsOnOrder with the total amount of units on order.

That was a pretty easy, but SUM can be used in many more ways so lets take a look at another example. You may recall from the previous blog post that the GROUP BY statement can be used the aggregate parts of a table. Well the same applies to the SUM function. Take for example the following problem. Say you need to get the sum of the freight cost for every country our company ships to and say we need the list to in alphabetical. Well by combining the GROUP BY statement with the SUM function to aggregate part of the table by the ship country, we can craft a query that allows us to aggregate part of the Orders table based on the ship country and then finally use an ORDER BY statement to sort our results alphabetically. 

And as you can see our query was successful in retrieving all the desired information.

Now to quickly recap. The SUM function is an aggregate function used to get the sum of the values of a specified column. The SUM function can be used alongside the GROUP BY statement to sum parts of a table as opposed to the whole column. Now that you have an idea of how the SUM function works lets move on the the next aggregate functions.

MIN & MAX

As their name's suggest the MIN and MAX function are used to calculate the minimum and maximum value of a column. Utilzing them is pretty straightforward, you can even use them in the same query to retrieve both values at the same time. Say for example you would like to get the price of the most expensive and least expensive product in our products table. Well, by using a comma to seperate both functions and entering the same column name into the parenthesis of each function, we come to a query that looks like this.

And as you can see the results table returns two values, the price of the cheapest item, and the price of the most expensive item.

Now just like all previous aggregate functions, the MIN and MAX functions can be paired with the GROUP BY statement to aggregate part of a table. In a quick example, let's say you want to get the date of the first order placed by each country. By following the pattern of previous examples you may have come up with a solution that looks like this. 

And our results table returns the date of the first order placed in each country shipped to.

Now to recap, the MIN and MAX functions are used to calculate the minimum and maximum value of a column or it can be paired with the GROUP BY statement to get the min or max of part of a table. Now that we've covered the SUM, MIN, and MAX functions, it's time to get into the fourth and final aggregate function of this blog post. The AVG or average function. 

AVG or Average function

If you've been paying any attention so far, you probably already have a good idea of how the AVG function works. But in case you haven't figured it our yet, the AVG or Average function is used to calculate the average of the selected values from a column. For example, say we need the average price of the products in our products table. You've probably already come up with a solution that looks like this. 

And of course the results table returns the average in a single value.

Up until now all of the examples have been very similar in nature to each other. All utilize a rather simple query to either retrieve a single value result, or utilize the GROUP BY statement to aggregate part of a table. Why not take it a step further and use what is known as a Subquery.

Subqueries

A sub query is simply put, a query inside another query. In our case, we're going to be using a subquery to essentially wrap the results of one query, and then use those results in another query to essentially preform two queries at the same time. So lets get started with an example. Say we need to get the average number of units sold from all orders. This query would have to be structured into two main steps. First we would need to get the sum of the the units sold for each order, then we would use those results to get the average of all units ordered. So lets start with the first step.

To get the total number of units sold we can write a query using the SUM function to add the quantity of units in the order details table. We would then use a GROUP BY statement to add them all by their OrderID. 

And as you can see, the results table returns a list of all orders with the amount of units ordered under a column named units.

Now that the first step is completed we can now work on making this a subquery to use the results of this query in another query. We would start by wrapping the entire first query in parenthesis, then we would assign a name to this subquery outside of the parenthesis at the end of the query like so.

What this has done is essentially create a new table with the information that we got from our initial query and in that table there is an OrderID and Units column. Now we can begin writing our main query which will use the AVG function to get the sum of Units from our subquery table by using the FROM statement before the opening parenthesis surrounding our subquery. We can then write our AVG function in our select statment as normal. The query should now look like this.

And you can see that our results now returns a single value which is the average number of units ordered.

Wrapping It UP

In this post I introduced you to four new aggregate functions and showcased how you can use a subquery to create a query that utilizes multiple queries within itself. Hopefully you are starting to get a grasp of what kind of information it is possible to retrieve with SQL. In the next post, we will move away from aggregate functions and look into using JOIN clauses to connect multiple tables in your query!