HAVING Clause

Quick reference

• 00:05 In this video, I wanna talk about the having clause.
• 00:08 And the having clause is sorta similar to the where clause.
• 00:11 The where clause helps us a lot, we could do a lot of stuff with the where clause.
• 00:14 But in the last video, we talked about group by.
• 00:18 And now we're sort of aggregating, we're adding things together and
• 00:22 making sort of new lists of things.
• 00:24 And when we do that, the where clause kinda breaks down a little bit.
• 00:28 It doesn't help us quite as much.
• 00:30 In that case, we're gonna use the having clause.
• 00:32 So a good example of that, we can take our current select statement.
• 00:36 We've got the count, the country, for countries, and
• 00:39 it's outputting the number of countries, right?
• 00:42 If we execute this, we get two records from Canada, one from Mexico, and
• 00:47 three from the United States.
• 00:49 So that's fine, if that's all we wanna know, we're good to go.
• 00:53 But now, let's say we wanna sort or filter this data by a specific thing.
• 01:02 Whereas in the past, where we would put where something is greater than this, or
• 01:06 where the first name is like John,
• 01:08 we can't really do that now because we've aggregated this together.
• 01:11 So instead of the where, we're gonna use having.
• 01:15 Group by country, having, and then we're gonna use that count from up here.
• 01:21 And we could put our brackets, and then country,
• 01:25 where the count of the country, let's say greater than 2.
• 01:30 So if we execute this, we get USA cuz USA has three people that are from the USA.
• 01:37 If we went, having country greater than, say, 1,
• 01:40 now we have two from Canada and one from USA.
• 01:43 There's only one for Mexico, so it's not included in this list,
• 01:47 it's not having more than one thing.
• 01:49 And now here we have our conditional statement, greater than 1.
• 01:52 We could put our greater than 1, we could put less than 1, or less than 2,
• 01:57 for instance.
• 01:58 Now if we execute this,
• 01:59 we're gonna get Mexico because we only have one record less than 2.
• 02:03 So we can use all of our conditional statements from before.
• 02:06 That was that list of equal, not equal, greater than, less than, less than or
• 02:11 equal to, greater than or equal to, etc.
• 02:13 So very, very useful, and if you sort of think about this,
• 02:16 you can use this for a lot of different things.
• 02:19 For instance, our simple little database table, if we pull this up,
• 02:22 let's see, edit 200 rows, we see all we have is country,
• 02:26 city, age, phone number, email address, first and last name.
• 02:30 We could easily have something like number of items ordered.
• 02:34 If this is a past customer database, we might have customers that have ordered 10,
• 02:38 15, 20 things from us in the past.
• 02:40 Or customers that have ordered three things from us in the past.
• 02:43 So might want to know, for
• 02:45 instance, all of our customers in the USA that have ordered more than nine things.
• 02:51 But the having clause allows us to do that.
• 02:53 It works with aggregation, with counting, with lists of numbers.
• 02:57 So that's the having clause, very important, very fun to play with.
• 03:01 In the next video, we'll look at the top clause.

Lesson notes are only available for subscribers.