Hey guys, in this video we will be looking at splitting groups using the having clause. Now prior to this, you would have learned that if you wanted to see only records that meet a certain condition that you could use a where, where command right after or from our well after our Inner Joins, then you could see we're and then stipulate your condition. Now what if you were faced with a scenario where you wanted to see only students that are carrying the minimum or what three credits, so only students with three credits, then by you know, instinct, you would probably say where and then you would take that column. And then you stay there condition where it is less than or equal to three. All right, or Well, you only wanted to see those taking three. So when you say equal to three, and then you would execute this, but then that would probably snap work because then you'll see here where it's still bringing back everything.

All right. And the issue is that when you're dealing with group by or aggregated data, then the where clause is not how you're going to go about filtering or splitting up this group's data. So that is where we have a new keyword called having. So I would say having, and then that would allow me to see the sum. Because remember that this whole query is based on a sum. And I want to see where after summing up all of the credits being taken by students, I am left with three that is my condition.

So I can't see where some and I'm going to try it and just show you what would happen. So I'm going to say where the sum is equal to three. And if I attempt this, then I just get an error. It says it's an invalid user. of group function. That's because the summary doesn't get executed until the group by gets executed or it gets incremented as the group by does its work.

So to just say we're so dumb is really not going to cut it because this is in the middle of the clumping exercise, it wouldn't be clean. So we have a keyword called having that allows us to stipulate the same kind of condition, except we can do it on the aggregate column this time. So we can say having some equals three. And then by doing that, after summing up, it will say Okay, give me back only those that meet this condition. And so we see here, we're only left with three. If I said six, then it would only bring back those with six credits.

And this having clause can be applied anywhere you have an aggregate function. If I go back to one of the previous queries where we Had min and max, I can see, give me back all of that data, but I only want where the minimum grade is greater than 50. All right, so I only want back all of the cloud all of this data where the minimum grade is 50 or more. And then that would, oh, that is actually giving me a tight Miss much error. I'm sorry, from the previous example. I didn't fill out the aliases.

And so that was causing the problem. So if I attempt this again, then there we go. So I'm only seeing the records that meet this condition where the minimum grade returned was over 50. And so you can apply this having clause if you feel the need that are having grouped, having performed some, you still need to filter out some of the the returns are the results that come back. So you can just use that having keyword and add your aggregate column and whatever condition it must meet. And you see that if I attempt to put in a non aggregate column here, it does not work.

So it will give you something on both an unknown column. And that's really because it can only look at the aggregate value as opposed to the value of being aggregated. And that is the end of this video. I trust you learn something valuable with the having clause and I know you put it to good use