Understanding Aggregation

[00:00:00]
>> Aggregation. So, we've kind of already been cheating a little bit. You've probably already seen it's just a really common kind of query to run. But let's go ahead and do a few of these right now. So, I'm gonna say SELECT, COUNT (*) FROM ingredients. If you ever wanna know how many rows you have in a particular database, that's what this does.

[00:00:34]
It's aggregating all of your rows together and then returning to you a number. You wanna know how many recipes we have? 56. You wanna know how many recipes photos we have, 44. And recipe ingredients, 170, right? COUNT(*) is something that you're going to see very frequently. What if I wanted to know how many different types of ingredients we have?

[00:01:13]
I don't wanna know what they are, I wanna know how many different ones we have. Well, here from recipes, sorry ingredients, I can say DISTINCT type. And this will return to me that I have four. It's not gonna tell me what they are, cuz I didn't ask for that, it tells me how many different types we have.

[00:01:37]
So this is where we're actually telling it what we want you to count. When you put COUNT(*) in there, you're just saying count everything, count everything the results set and give that back to me. Just for clarity sake, if you wanna see what those types actually are, you can say SELECT DISTINCT type FROM ingredients.

[00:01:56]
And this will actually give you the four different types. What if I wanna know how many of each type there is? Seems like that would be useful information to know. So, here I would say SELECT, let's Ctrl+L this so you can see. SELECT. Type, right we wanna know the type and then we wanna know the count of how many of each type there is.

[00:02:22]
So count type, I think that tracks, right. From ingredients, now we have to give it like a little bit of a hint is, how are you going to group these things together, right. That's where you enter this GROUP BY, a group by is always going to be accompanied by some sort of aggregation function, count as an aggregation function.

[00:02:44]
Another one that you might, use a lot is average or min or max, right? It's taking a bunch of things and kind of condensing them down or, in other words, aggregating them into some higher level insight, right? Here we're gonna group by type, right? Because that's actually what we're trying to count here.

[00:03:04]
We have 29 vegetables, 78 fruit. And meat 6. Now you get into this weird situation of, let's say, I was trying to do a query of something like this, SELECT, title, type, COUNT(type) FROM ingredients, GROUP BY, type. This query doesn't work, why?
>> Cuz the title could be different for all of the, different groups.

[00:03:44]
>> Yeah, it should be different, right? It actually must be different, because there's a constraint on the table that forces them to be different. So you need to ask yourself, what is this actually really asking. The title here is nonsensical because it's, the title is being aggregated away into this type and account type.

[00:04:02]
So asking for the title here it's nonsensical to ask for one, because it couldn't possibly, you're not really asking for anything. Or you're asking for something that doesn't make any sense. So if you do this, it's gonna be like, I can't do that. Because the ingredient title at that point would have to actually show up in the group by for that to make any sense, right?

[00:04:21]
So in other words, if you're gonna be asking for something in a SELECT and you have a GROUP BY. Everything that's gonna appear in the GROUP or in the SELECT probably needs to be in the group by as well, because you need to be grouping by things. Grouping by title wouldn't make any sense because, everything would have a count of one, right.

[00:04:40]
So, I found that really confusing when I was starting out with SQLs, I wanna select things that aren't in the GROUP BY and then I kind of head over. Have this light bulb moment of, what am I trying to do here? And it didn't make any sense. We'll see more of this as we get into using the movie database.

[00:04:55]
We don't really have lot of things to count on the recipe database. But I'll show you how to do things like averages and other things when we get into Window functions, which are closely related to aggregation.