Exercise- Aggregate Function and ROUND in SQL

So let's see how this works. So this is going to be 1.2 aggregate functions and round. Because we're going to get some numbers.

We're going to want to round them. Now, before I get started, I always have to choose my server up here. Noble Desktop server.

It defaults to company data, which is fine. Most of the time we're going to be sitting in company data. Every once in a while, we'll hop over to jeopardy to do a little something with jeopardy, but we're not going to be there very often.

So in company data here, let's say my list of products. If I show all of my products here, I've got all of these prices. And if I just look at the price column, there are a lot of values in there.

Out of all of those, I can say, give me the maximum price. And out of all of those, it will find the most expensive price. Or I can say, show me the lowest price out of all those values.

The idea of aggregate functions is many things go in, but one comes out, you boil it down to one resulting data point. You don't need to know all of them. You want to know something about them all.

Yes, you want to know which product it is. Right?

Because I might want to know that answer to say, "Hey, let me go find products with that price, " but we have to do something a little more advanced, which we'll talk about later. I don't know if we get to that today or tomorrow, but we'll get to that later. We're not quite there yet, but we're getting there first.

You had to figure out what the cheapest thing is, and then you need to do something more with it. Now here's an interesting thing. A lot of times when I show people how you can put the same column twice, they say, "Why would I ever want to do that? Like, why would I want to list price twice?" Well, because one time you might want to say max and another time you might want to say min.

And so now in one line, I can see the max and the min all on one line. Is it using the same data? Yes. Is it doing the same thing with it? No, but you can reuse your data and do different things.

I could add the average price so I can know the maximum, minimum, and average, all in one line. That's pretty cool. I'm just doing multiple different things with price.

This is why I say you are creating a result set with your query. It is not a view into the table. It is using data from your tables.

It is using data from the database to manipulate it, to create whatever you want, to show a result set of your choice. So you're not just viewing the database. You're using information to do what you want, because this technically is like that in the database somewhere.

I just need to use the information in the database to create this. Now, one thing that is important to understand is that when you put a whole column into an aggregate function, you only get one value out. So what I think people sometimes try to do is let's say you're listing price twice.

Let's say you only do the aggregate function on one of these. Before I even run this, let me, let's think about this. Price is a whole column.

What's this column going to look like? Just, just one value. You get an error. It says, "Wait, no, wait a minute."

You can't have one column with one value because max price produces one value and then have another column with many values. It's not just going to repeat that over and over again. It doesn't work that way.

So you can't mix and match columns that have different numbers of rows. Now you might say, well, but in Excel, and I'm like, but we're not in Excel. I don't care if you can do things in Excel.

Because we're not there. It doesn't work in SQL. So right, exactly.

If you aggregate one, you have to aggregate them all. It works this way because all of them produce just one value, right? So I don't have some columns with multiple values and some columns with one value. They're all one value.

So it works: if you're going to aggregate one, you have to aggregate them all. You can't just add another column that has another variable amount of rows that doesn't match, doesn't work that way.

Okay. Now let's say we want to count. If I get all of my users, think about the number of rows in this result set.

I'm just saying, show all the columns here. It's fine to show all the columns, right? These are all the user data. Instead of trying to look down here and see a hundred, um, DBeaver only fetches in increments of 200.

So down here, it's only showing you how many records it's currently fetched, but let's say you have a million users. You're going to only fetch users in increments of 200 at a time. The point here is I don't want to see all my users.

I just want to know the count of how many users I have. I want to count all the rows in this table. And if you were downloading the user data of a million users, think about how long that would take to download.

That could take a while to download. It's a lot of data, but instead of what I want to do is I want to count that. So I put a count around it.

And now I'm counting the number of rows in that result set. So in this case, it's a hundred because I have a hundred users, but that could be a hundred million and it would retrieve it very quickly because it counts it. And you only download that count.

You don't download the original data that goes into counting that now you can count a certain column. And let's talk about why you might want to do that in certain cases, because it can be useful in certain cases. You may want to avoid it.

So remember with passwords, remember there's some, there are some weird things about passwords. Like sometimes they're empty strings and there, remember how there was one, no, at the bottom, just one, no. We now know that there's a hundred users.

If I look just at the password column here, just to the password column, I'm only looking at that. This is my result set. If I count the number of rows in this result set, let's see what we get.

99, any guesses as to why it would be 99 versus under I put it on the screen. No, null values are not counted. Empty strings are counted because it's still something.

Null is the only true. Nothing. So null values are not counted.

Assuming the entire row is null. That's important. Assuming the entire row is null. So that's why when I counted users, I still got a hundred because there was no entire row that was null, but when you look at the password column alone, you run that risk that technically an entire row because it's just one column that the whole row is null.

And then it won't be counted. Now that could be, could be good or it could be bad depending on what you're looking at. If you want the total number of users, why bother looking in a certain column? I don't need to look in a particular column.

I may as well just look at the entire users table because what are the chances that an entire row would be null? Well, I got a primary key. There's no way that the entire row could be null.

No, that's impossible. I could have nulls in certain cases. Like maybe somebody doesn't have a name.

Somebody doesn't have an email. Maybe somebody doesn't have a password, but there's no way that the entire row is going to be null. So I'd rather do this on all of the columns and not limit myself to one particular one.

When would you maybe want to do that? Well, let's say I select everything from users here. So these are all my users. Now let's say that I look at my user states, every user lives in a state somewhere, right? So right now, if I were to count this, it would count the number of rows.

And since I have a hundred users, I would get a hundred states. So just to show this, if I count this, I'm going to get a hundred because I have a hundred users and every state is from every, every user is from some state. Now, if I want to eliminate duplicates, I add the word distinct, right? If I add distinct here, before I do the counts, now I have 43,43 because it got rid of the duplicates.

Instead of looking down there, if I say to count this, it tells me 43 because I'm counting distinct user states. So that's why you might want to count a particular column because you want to limit it to a particular column to count the values that are in there. Okay.

And you know, throwing in distinct in there because we want to count distinct States, not just count States in general. Okay. When you are creating a result set, these are the number of rows that are in the result set based on whatever filtering or whatever you might do.

The asterisk is saying, these are all the columns. Now, when I'm showing all of those columns, because I have a lot of columns, if the entire row of every single column, if every single column were null, when I do a count across that result set with all of the columns there, the entire row would have to be null for it to not count it because I am counting, showing all of those columns. However, if instead of using asterisk, which would show all the columns, which would means all of them need to be null.

If I restricted to password only, now I only have one column and that column could very easily have a null just in that one column counts always counts rows. But the idea is if an entire row is null, then it won't count it. If you're only looking at one column, there's a very good likelihood that there could be a null in that one column, but the more columns you add.

So asterisk having more columns, it diminishes the chance that the whole row will be null and therefore not counted. Count always counts, always counts rows. You, you, there's not a like count columns feature because rows are, rows are records.

That's what we want to count, right? That's, that's what we're caring about is the number of rows. The only reason we're putting it around columns, because I know that can be a little confusing. Like are you counting those columns? No, you're not counting columns.

Counting always counts rows. The only reason we care about the columns is because it limits how that counts is created, right? It affects how the counts is created, because if we're counting rows first, we have to create a result set that creates rows, right? And if, for example, you know, like, like what we did down here, if we said, no, down here, right. If we create fewer rows because we say distinct user states, we did something that created fewer rows.

And all we're doing is adding count to count that result set, right? Because that has more rows. This has more rows, right? When we add distinct that says eliminate duplicates. We get a lot fewer rows.

And so when we're counting that result set, we're counting the number of rows in that result set. If we count just on that column, we've got a hundred users. Each has a state, but if we count distinct, there's fewer rows in that result set, but count always counts rows.

Okay. Now let's say we have the price in the line items table. These are all my prices.

If I want to add all of those up, I can add up all those prices, but let me just show you the difference here. If we did some of price times quantity, wow, 39,000 versus 99,000. If you come back to your CEO and said, "Hey, we only made 39,000."

They're like, "Oh no, we're going bankrupt here. Like where's all of our revenue. It's not enough."

If you don't count in the quantity, right? Price times quantity, that's the value of your line item. That's how much revenue you're making. And so it will do either just this one column or it'll do this math, create that whole column and then add up all the values in that column.

Just adds them all up. Many values go in, but only one value comes out. That's what aggregating means to put it to sum it up into one summary, single, single value.

Now, on the other hand, if I want to do this as average, average here, if I'm talking about the average price points versus the average value of the line item, the average price point could help me decide, okay, on average, people are buying products that are $54. My new product is above or below that average. So that might help me choose my new price point for my new products versus on average, people are spending $135, including how many things they're purchasing, right? They're on average spending on an individual line item, $135 total, different things.

Now notice how it just says average. That's not very nice to just see average. Like what the heck is that? Wouldn't it be nice to have nice names for these things, but that's where we can put on column aliases because these columns don't exist in my database, but I could say, let this be known as and then make up a name.

Maybe I call this average product price. And so now I have a nice name. Now, if you want to put spaces, you've got to use double quotes, then you can put spaces in your name.

If you want to make it look pretty nice, you can do capital letters if you want, but you got to use double quotes, not to be confused with single quotes because single quotes are for strings for names. You got to use double quotes. Also as is always optional.

If you want to leave it off, you can still works. If you want to leave it on, that's fine. This one could be known as average line item value.

So that way I'm explaining these things, especially if I'm going to export this data to do something else with it. Might want to explain that to people. So you can give aliases nice column names so people can understand what this stuff is.

Now, these look like great prices, right? Look at how many decimal places they are. There's way too many decimal places with these things. Okay.

So I want to round these numbers. Technically I could use cast to do it, but cast is meant to convert data types. This is in the correct data type.

Like these are numbers. So I want to use the rounding function. So there's actually a rounding function that we can use.

If I want to round a number, and we're going to ignore SQL Server for this stuff, because SQL Server is a bit different in what it does. We're just going to ignore all that SQL Server stuff. We're just going to do the PostgreSQL one.

And I'm just going to do average value here. And I'm going to do two columns. I've got to do one column without it rounded and one column with it rounded, just so you can see the difference.

So here, this is the average price. And I do another column and I'm going to round and I'm going to round that average price. And notice that goes to 54.

So it goes from 54,20 to 54. If you don't say how many decimal places you want, it rounds it to a whole number. So it's going to round up or down to the nearest whole number.

If you want to round to a certain number of decimal places, that is an optional thing that you can do. You can give it an optional parameter. When you have functions like this, functions expect you to tell it certain things.

In this case, the rounding function at a minimum needs to know the number that you're rounding. And if there's anything else, you can make a list of different parameters. The second optional parameter is how many decimal places.

And if you add a comma and the number of decimal places, now it will round to a number of decimal places rather than a whole number. But if you don't say it'll just round to a whole number. The other thing that we could also do is right now, this is just an, a normal number.

If you wanted to do this in a little bit of a different way actually, I'll just add as another column here, I could cast to convert it into a different type of data. And I could say, cast this average price as money, money adds a dollar sign and money is always two decimal places. If I convert it into money, it displays like money.

And I didn't even have to say to convert it into two decimal places. It just does that automatically. That's a different type of data.

Is it, is it a regular number or is it money? That's kind of cool. It not only looks nice, but it has the right number of decimal places. Show me the money.

So that's one example of casting convert from a regular number to a money kind of number SQL Server. We're just going to ignore all of that because SQL Server works a bit differently. So don't worry about all that SQL Server stuff.

All right. So let's keep going with these aggregate functions and rounding as well. We had just seen about rounding.

So let's talk a little bit more about the idea of rounding. We were talking about average price, right? If you are looking at your line items and you are getting the average price. So essentially you're saying out of that price column, I want to take all those values.

And I want to figure out the average. Notice how each step I do here filters our data more and more. I like to do it each step by step.

So you see what each little piece of code does. We've gotten one value out of here. Now sometimes you just want to do a rounding on that.

And so you could say, well, I want to round this price because I want to round it. If you say round, it gives you a whole number. Maybe that's close enough.

Maybe you'll run around it to two decimal places. But what if you want to specifically round up or round down? So sometimes, for example, if you're doing taxes, they'll say round up or round down. They don't accept that it rounds up or down.

According to 0.5 and above get rounds up, gets rounded up or below 0.5 gets rounded out. You sometimes need to say round up always or always round down. If you always want to round down instead of them having a round up or a round down feature, they call it ceiling and floor.

If you look up, you see the ceiling. You look down, you see the floor. So floor will round down.

Why didn't they just call it round up? I think part of it goes back to round up is more characters than floor. And you might say, well, that doesn't really matter to me nowadays. But if you remember back to the early days of computing, when computers took up the space of a room, every byte mattered.

So back in like the seventies, when we had megabytes or even kilobytes of hard drives, we didn't have the gigabytes and terabytes that we have today. Every character mattered. We wanted to save and be the most efficient possible.

Remember when SQL was created? 1974. This has been around for a really long time. And a lot of programming languages like JavaScript and stuff, they've been around for a very long time.

And so some of the languages will even say seal S oh, sorry. C E I L not ceiling because it's fewer characters and it's also less typing for a programmer as well. So, um, yes, they could have called it round up and round down, but we didn't make the language.

We just have to learn the language. So instead of round, which will go up or down accordingly, if I say to floor that will always round down and that does not take another parameter floor will always round down. If I look at the average price here and compare it to the floor, the floor of it.

So it started at $54 and 20 cents. Now we've rounded down always, or if I want to round up, I can say ceiling to round up. And that will always round up.

Notice that goes to 55. So the round down goes down and the round up goes up with floor and ceiling. Now, unlike the nice rounding function, where you can simply add another parameter for how many decimals you want to go to.

Wouldn't that be lovely? It doesn't work. Like you added that feature to round. Why did you not add that feature to floor and ceiling? That would be really nice.

Right? They're like, but you can do math. Can't you just do the math to figure it out? Like, I know I could, but I don't really want to, but thank you for making me do that and make you make it take a lot longer and a lot more code to do that. So I don't know why they don't go back to these very fundamental features.

A rounding up and rounding down, but I don't think they're ever going to go back and improve these basic features to add the ability to round to a certain number of decimals. We have to know the math to be able to round to a certain number of decimals. Sorry, I'm going to make you do a little bit of math here.

If we want to do multiple decimal places, we're going to ignore the SQL Server. We're not doing that there. And I'm just going to do the round down.

So I'm going to get rid of my ceiling. Okay. So here I've got 54,20.

And if I was always going to round down instead of the two Oh five, so rounding down would go to 20, right? Rounding up would go to 21 sets. So right now I'm getting a whole decimal. Oh, sorry.

A whole number, a whole number. It's an integer. If we think about parentheses, parentheses say, do what's in the parentheses first, before the things that are outside the parentheses.

So price, if I say price times 100, it multiplies that price times 100 before it does the average. I essentially moved the decimal place over by two decimal places because I multiplied by 100 a simple way to remember it is how many ever zeros is how many ever decimal places you move it over. So instead of 54, I made it 5,420.

Now after the floor is done to move the decimal back the other way, if multiplying moves it one way, dividing moves it the other way. After the floor has been done after the rounding. So outside of the parentheses, I then divide by 100 and I ended up rounding it to two decimal places.

Now it's 0.20 and they figured the zero is not necessary. So they just get rid of that, but it did work. For example, if I switch this over to ceiling to round up, notice they round to 0.21 just to prove that it works.

So the idea is that before you do it, you have to move the decimal over and then after you do it, you have to move the decimal place back and how many ever decimal places you want. That's how many zeros you add. If you just want it to be one decimal, one zero, now it's just one decimal place.

You want three, I have three zeros. The number of zeros is the number of decimal places you'll get. You know, like, couldn't you have just added another parameter to the basic functions? Like they could go back and add those.

Like it's been 50 years. You'd think somebody would want to make those basic features better. They wouldn't break any code that's out there.

If they add an optional parameter, right? I'm not holding my breath. If they haven't done it in the past 50 years, I doubt they're going to do it in the next 50 years. I think they figure it done.

It's finished; I don't think anyone is thinking about these things. So unfortunately, we have to do some math.

So you go to the SQL Server stuff. All right. We did those.

Okay. Now let's go ahead and open up this 1.2 aggregate functions and round. And we've got some challenge questions here for you.

And we'll give you some time to go through those and then we'll come back and discuss those solutions. So 1.2 aggregate functions and round. Let's go through that file.