 Hey, what's going on everybody? My name is Alex Friedberg and in today's video we're going to be going over the group buy and the order buy statements. In previous videos we created tables, we went over the select, the from and the where and now we are at the very end of our SQL basic series. If you stayed with us for the whole time, hopefully you have learned a lot and learned the basics of SQL. In future videos we're going to be going over intermediate and even more advanced concepts and even going through portfolio projects that you can use to put on your resume. If you like this type of content be sure to subscribe below but let's get into it for today. The group buy statement is similar to the distinct in the select statement in that it's going to show the unique values in a column. The difference is if we say distinct gender, what's going to be returned is the very first unique value of female and the very first unique value of male. But if we say gender and we say group buy gender, it's only going to return two values. But in these two values we actually have all the males rolled up into this one row and all the females rolled up into this one row. Now let me further show you what that means. If I say count of gender, now you can see that this whole time there were six males in this one row and there were three females in this one row. So with the distinct it really is only showing us what value is in there that's unique. But with the group buy it's showing us what the unique value is but it's also rolling them all up into one column so that we can use it for other things. Now real quick I want to be able to see both of these at the same time. So let's just put this up here and let's run this so we can actually see both. Now let's add age to this statement down here or this query and let's only run this one. I want to show you what happens and why it happens. We're now looking at gender, age, and then the count of gender. So if we look down here we only have one male who is 29, we have one male who is female that's age 30, and so on and so forth. So none of these people are both the same gender and the same age. If for example we had two or three people who were male and who were 30 years old then we would have a two or a three over here. So this count is actually being counted at each row that's being returned. So for our data that we have today this isn't a fantastic example because it really split it out. There were any that were the same but as you can see you can put multiple columns as long as you put multiple down here. Now why did we not have to put this count gender down here in this group by? That's because this count gender is actually a derived field or a derived column. It's derived based off the gender column. So it's technically not a real column that's in the table. It's one that we're creating that's fictional per se. So the age and the gender are actual fields or actual columns that are in our tables they have to be down here. And like I said before it's the comparison to that distinct in the select statement because we're looking at the distinct of gender and age. So we're saying distinct across multiple columns both gender and age. And as we had before we were only looking at gender it's going to roll all of those up into just male and female. But if we want to add more we can easily add more. In this group by statement we can still do things like where age is greater than 31. We can still do those things. So let's execute this and our numbers are going to change. So we're doing it based off gender and we're looking at the count of people whose age is greater than 31 which is smaller than before. Now let's look at order by. I'll do it down here really quick for demonstration but I am eventually going to come up here and use it because I think it'll be a little bit better. To completely round out this query down here let me give this a name. Let's do count of gender. And then let's come down here and let's order by. Let's order by count gender. And when we run that it's going to do 1, 3. And that's because as a default SQL has an ascending feature which is going to be smallest to largest going down. If we want to change that we can change it to descending. That's going to be largest to smallest. So now I have 3, 1. And if we want to do it based off gender and we do it descending. Now we have Z to A. And so that's going to be male, female. And if we get rid of that it's going to do the default ascending. And let's see what that brings. Female, male. Now for what we're trying to do let's look at this large table. So I think it's going to be a little bit more descriptive or a little bit better visually. Let's do order by and let's do age. Let's run this and it's going to order smallest to largest. If we do descending it's going to do largest to smallest. Now you don't only have to do just one thing. You can do multiple columns. So if I wanted to do age and then gender I can do that as well. So let's do gender and let's run that. So now we have the age but under the age we also have it ordered by female and that's in ascending order. So A, B, C, D, F, so females first. So it's going to be female first and then it's going to be male. And again female and male. Now we don't have to just let it be ascending for each one. If I wanted to do it reverse in this column I can do descending. Now let's run that. And when we have 30 now male is first and female second. And if I wanted to do that over here I can do descending. And now we have them both descending. So it's going to go top to bottom and when we have 32 it's going to be male, 32 female. So you can specify lots of different things in here and we don't actually have to use column names. We could just use numbers. So if I wanted to do 1, 2, 3, 4, 5 I could. But let's try to replicate the exact same thing before. This would be column 1, 2, 3, 4. So let's do where 4 descending and then let's do 5 descending. And if we execute that it's going to give us the exact same result as if we had actually put in the column name. And I do use this a lot. Oftentimes I don't use the column name. I just, if it's a small table I'll just use the number. So in my actual queries I do this a lot where I just use the number instead of the column name. So that is the group by and the order by statement. And if you have watched through my previous videos you should be completely done with the basics of SQL. So congratulations. The next thing to do is really just practice the basics. Because the basics are what you're going to be using day in, day out. And so what I would recommend is create a few more tables, query those tables, try to think of use cases and what you would actually want to know from that information. After that I would move on to my intermediate videos if those are already out and then I would move on to my advanced videos. Those are going to go over some more challenging topics but things that would be very useful for anybody to know. In my next video I'm going to be going over intermediate SQL topics. Things like joins and subqueries and a ton more. So if I already have posted those be sure to go check those out on my page. And if I haven't I hope to have those up soon. Thank you guys so much for watching. I really appreciate it. If you learned anything in this basics of SQL series be sure to subscribe below and I'll see you in the next video.