In the previous guide, we updated our guides_revenue column to work with financial data, and now we're going to start going through our functions. The very first one is one we already tested out, and that's the MIN function.

This is going to bring the smallest value from a column that we're looking out at. I want to reiterate what an aggregate function is because aggregate functions sound very scary, but in all reality, they aren't. They are just aggregating data, and they're giving back a single result. In other words, if want to look at this min function.

It is going to aggregate a collection of all the items inside of the guides_revenue column, and then it returns a single value. So that is why it's called an aggregate function. It's taking a lot of data, and it's compiling it down and running some computational formula over it.

Then it returns a single value, in this case, it's the smallest item. Now let's look at the next function. We've already looked at it a little bit, and that is the max function.

Now if I run the MAX function. It is going to find the largest element in the data set, and we know from our guides table that that is 5,000. So that is working properly. Now let's go and take a look at a few more financial types of items, or you the ones you could use in a financial type of application.

Obviously, you could use this in any application. I usually find myself running this type of queries with financial data. So the next function is average. It does pretty much exactly what you'd expect it to do. Now run this function, It is going to return the average value.

This is why it's important that I went with the decimal data type instead of an integer or a float because it allows us to have more precision. You see where it goes four decimals deep. We can see that this is the exact average of each one of the guides in our database.

Now if I copy this and come down for the next one the next one's going to be some. SUM is also going to do pretty much exactly what you think it would do. It is going to tally up all of the values inside of the database, and it's going to give you a total. Now run this.

Right here it got the sum of all of the revenue in our guides database. It is ninety-five hundred dollars, so that is working nicely. Now we have one other function we're going to look at, and that is the count method.

The count function is a little bit different, and I’m going to use a different table here. When you say select count from all (SELECT COUNT(*)). The reason why I'm passing in all here is that we are looking for the actual count of values. We're going to say I want to see how many users are in the database.

I’m going to say SELECT COUNT(*) FROM users you can see that went through the system and it counted up the users, and it returned 2003 users in our database. I find myself using the count function constantly. Many times on a daily basis because anytime I want to see how many users I have in the database or want to see how many guides that have been written or anything. Using the count function is a very common task.

These are going to be used day by day. The functions you're going to use daily are average, sum, min, max, and count. Usually when you're dealing with financial data or anything that has some integer or decimal base type of record. So that's going to be a nice set of functions for you to become familiar with. I recommend for you to play with them on your local machine and see what type of results you can get from them.

Now the other nice thing that you can do here is as nice as being able to run SELECT COUNT(*) FROM users and is helpful as that is. Many times you want to do something that's a little bit more specific. This shows you all of the users in the database but what if we wanted to do something a little bit more specific?

If I say "select count of all from our addresses database and say where addresses state is New York." `SELECT COUNT(*) FROM addresses WHERE addresses_state = ‘NY’;'. I believe we should have three records here and if I run this, you can see our count is three.

So each one of these aggregate functions has very cool things about them. They're not just for collecting data on the entire table. I usually use these with some level of specificity with a where clause, with a nested subquery, or anything like that. That's where they come in handy is when you run those types of queries.

Code

USE devcamp_sql_course_schema;

SELECT MIN(guides_revenue)
FROM guides;

SELECT MAX(guides_revenue)
FROM guides;

SELECT AVG(guides_revenue)
FROM guides;

SELECT SUM(guides_revenue)
FROM guides;

SELECT COUNT(*)
FROM users;

SELECT COUNT(*)
FROM addresses
WHERE addresses_state = 'NY';