GROUP BY Clause

Quick reference

• 00:05 In this video, I wanna talk about GROUP BY.
• 00:07 And GROUP BY is sorta similar to ORDER BY, but slightly different.
• 00:11 So, first off, you'll notice a change that I made to our database,
• 00:15 I added this Country column.
• 00:16 And to do that, I just went to Customers, Current database, Design and
• 00:21 I typed in Country, give it a varchar 50 and then save this.
• 00:26 And then I went here to Edit Top 200 Rows, and
• 00:29 all I did was scroll over here and add some countries to all of our people.
• 00:34 So we have USA, USA,USA, two Canadas, and a Mexico.
• 00:38 So, go ahead and close this.
• 00:40 Now we just have this six records, no big deal.
• 00:43 But you may have millions of records, as I've said over and
• 00:45 over throughout this course, thousands, millions, lots.
• 00:48 And you may wanna know, for
• 00:50 instance, how many customers do we have in Canada, how many do we have in Mexico.
• 00:54 How many do we have in the USA?
• 00:55 Very, sorta, normal thing to wanna know.
• 00:58 Well we can use the GROUP BY for that, and it's a little bit more complicated
• 01:02 than some of the other stuff we've looked at so far.
• 01:05 But let's say we wanna group by Country, okay.
• 01:09 So that's only part of it, now we have to come up here and
• 01:11 we have to sort of change some things around.
• 01:13 So what we want is a count, we wanna know how many.
• 01:16 So we use count and we're gonna say COUNT the Country, And
• 01:23 we should probably put that in brackets, though I'm not sure it really matters.
• 01:28 So, give us a COUNT of Country and then GROUP BY Country.
• 01:31 And this is also gonna be ascending or
• 01:33 descending, so the ascending is the default as normal.
• 01:36 So now, if we come up here and hit Execute,
• 01:38 we get this funky column and okay, it's kinda hard to read.
• 01:42 We don't know what's going here, so let's change this around a little bit.
• 01:44 Let's come up here and add another column of Country.
• 01:48 And if we execute this, now we get this column, so
• 01:51 it corresponds with each of these.
• 01:53 So now we can tell what's going on here.
• 01:55 So what it's done here is it's created a column.
• 01:58 And it's told us that we have 2 customers from Canada, we have 1 from Mexico, and
• 02:03 we have 3 from the USA.
• 02:05 Now if we, I'm gonna copy this stuff, and
• 02:09 if we get rid of all this and just go back to our star.
• 02:16 So remember, 2 from Canada, 1 from Mexico, and 3 from USA.
• 02:19 We Execute this, we see, yes in fact, there are three from USA,
• 02:23 two from Canada, and one from Mexico.
• 02:25 Which is exactly what the report just told us.
• 02:29 So that's GROUP BY, go ahead and paste that back in there.
• 02:33 It's very sorta similar to the ORDER BY, but
• 02:37 allows us to create different sorta reports with these counted things.
• 02:42 So very, very interesting.
• 02:43 We have country, I guess we could try going
• 02:49 ORDER BY COUNT, Country, DESCENDING.
• 02:54 So USA, Canada, Mexico, 3, 2, 1,
• 02:59 descending from high to low or ASC for ascending, 1, 2, 3.
• 03:05 A lot of these things that I’m teaching you can sort of pile on top of each other.
• 03:09 Like right here, where we piled on the ORDER BY on top
• 03:13 of the GROUP BY on top of the, on top of the, on top of the.
• 03:16 And as we go on and learn more of these different clauses and things,
• 03:19 you'll see how you can sort of string them together and in an interesting and
• 03:23 useful sort of way.
• 03:24 So that's a GROUP BY, scoot this one more time.
• 03:27 Very useful, and the next video we'll move on to a whole new clause,
• 03:31 the HAVING clause.

Lesson notes are only available for subscribers.