SQL Basics Tutorial For Beginners | Group By + Order By Statements | 4/4

Key Takeaways

don't on everybody my name is Alex free Birnam and today's video we're gonna be going over the group buy and the order by statements in previous videos we created tables we want of the select the from and the where and now we are at the very end of our sequel basic series if you stayed with us for the whole time hopefully you have learned a lot and learn the basics of sequel in future videos we're gonna 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 by statement is similar to distinct in the Select statement and that it's gonna show the unique values in a column the difference is is if we say distinct gender what's going to be returned is the very first unique value of female in the very first unique value of male but if we say gender and we say group by gender it's only gonna return to 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 room so with a distinct it really is only showing us what value is in there that's unique but with the group by 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 then I want to show you what happens and why it happens we're now looking at gender age and an account of gender so if we look down here we only have one male who is 29 we have one male who is female as 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 2 or a 3 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 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 in 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 but the gender and age now 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 gonna change now 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 gonna come up here music 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 gonna do one three and that's because as a default sequel has an ascending feature there's gonna be smallest to largest going down if we want to change that we can change it to descending that's gonna be largest to smallest so now I have three one 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 gonna be male female and if we get rid of that it's gonna 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 gonna be a little bit more descriptive or a little bit better and visually let's do order by and let's do age let's run this and it's gonna order smallest to largest if we do descending it's gonna 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 ABCD F so females first so it's gonna be female first and then it's gonna 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 two sending and now we have them both to something so it's gonna go top to bottom and when we have 32 it's gonna 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 one two three four five I could but let's try to replicate the exact same thing before this would be column one two three four so let's do where four descending and then let's do five descending and if we execute that it's gonna give us the exact same result as if we'd 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 in the order by statement and if you have walked through my previous videos you should be completely done with the basics of sequel so congratulations the next thing to do is really just practice the basics because the basics are what you're gonna 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 gonna 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 sequel topics things like joins and sub queries 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 sequel series be sure to subscribe below and I'll see you in the next video