Aggregate Functions in SQL

Aggregate functions. So aggregate functions are extremely important. We're going to use these a lot because it's not just enough to find things.

A lot of times we want to know more about stuff. We want to do calculations, meaning we want to add things. We want to find the most, we want to find the least, we want to find the average.

If you've ever been in an Excel file, probably the first thing you do is you take a column and you add everything up. You do a sum function. So think of that.

That is an aggregation. When you're aggregating things, you're putting together a lot of data in a pile and you're saying, let me do something with all the stuff in that pile, right? In that column, essentially. So you want to add all of the things.

You want to find the most, you want to find the minimum. So, so many times, very, very often when we do a query, we want to find those results and then we want to aggregate them in some way. So these are the aggregation functions that we have.

Let's take a look. Let's say you have a quantity column. You can count all of the values in that quantity column.

And if you add, so if you count them, you're counting the number of rows. So counting is very simple. How many rows do you have? For example, if you want to know how many users you have, you'd want to count the number of rows in the users table.

If you want to know how many orders you'd have, you'd have to count the number of rows in the orders table. If you're counting, you're counting the number of rows. So 1,2, 3,4, 5,6, 7,8, 9 rows.

That's the count. Sum would add up all the values. If you add up all those values, if you don't trust me, you can do the math, but that's 42.

The maximum. What's the highest value out of all of those? Nine. That's the highest value.

What's the lowest value? Two is the lowest value. What's the average value? So in other words, add it all up and then divide by the count.

That's 4.6. So 42 divided by nine is 4.6. So these are all the types of aggregate functions that we can do. And we do these a lot with things. For example, you might want to know instead of showing me the orders that were shipped to Florida, just tell me how many I had. Like, are you going to sit there looking through tons of orders? Like we've seen how we can say, "Let me see all the orders where the state is Florida, " but you'd have to look through all of those orders.

Do you want to see all those orders or do you just want to know how many you had? You probably just want to know how many you had. We often want to aggregate those or just add them up. We could just sum—well, no, not sum them.

We do want to count the number of orders, right? We want to count those up. What was the most somebody spent? So you could add up all the things that people spent and then what's the maximum amount? So out of all those values, what's the most, or what's the least? You could find the minimum.

So for example, if you want to find the most expensive priced product, you could say, look in that price column for the maximum price. What's the most expensive product listed for sale in my products table? That would come back with the most expensive price.

You don't have to sort by that column. Look at the top line. It just gives you one thing. What's that maximum price? Or you can say, what's the cheapest price?

Now you can know the cheapest and the most expensive priced product. If you want to know how many orders you had shipped to Florida, you still want to do a WHERE the ship state is Florida, but instead of just seeing them, you can say to count—always count rows. We're counting every single row in that result set.

So that'll give you the count. So you know how many orders were shipped to Florida. If you want to know how much money you made off of your line items, like what's our revenue?

You can add up all the prices in line items. Now something seems a little off here. What mistake was made in this query? If I'm adding up my revenue in line items, you can add integers.

You can add decimals—but yes, somebody said we forgot the quantity. Yes, exactly—price times quantity, price times quantity.

Yes, if you're just adding up the prices, while you would get all the prices, if you don't include the quantity, your math is going to be wrong. You're missing out on a lot of revenue.

So in here you could say price times quantity. I just like to include these little things just to see if you're paying attention. Now average price, this could be good because if you're looking at products that I've sold, maybe you're trying to decide, "I'm going to make a new product."

And I'm trying to think what would be a good new product price point for this product. And I want to see what's the average price of things that people are actually buying. Because if I look at the products table, those are products that I have for sale, but I haven't necessarily sold them.

In line items, those are things I've actually sold. If I get the average price, you know, some people might be buying a lot of cheaper products. Maybe they're buying more expensive products on average.

What's the average price point? Because I want to see if my new product is higher or lower than the average. Maybe if it's lower than the average, I might say, "Hey, you know what? Maybe I can afford to charge more for this and make my profits more." So that might be perfectly fine if I'm choosing a price for a product.

But if I'm looking at the average value of a line item, I'd want to have price times quantity. It depends on my question as to what my solution is. So I have to understand the intent. There's always some question that we're trying to answer and we're using SQL to answer that question.

So it all goes back to, what are we trying to figure out and how do I use SQL to find that answer?