![]() ![]() | first_name | last_name | cell_phone | birthday | house_id | Mysql> select * from friends order by last_name We might as well get right down to testing this out! This clause let’s us, you guessed it, order are results by a specific column. This is one of the more common ones you’ll come across. So since there are two Farmingtons, two Olivios, and two Penblancs, they they are the ones who get returned in our results. What happens is by using the having clause in our example, we are telling MySQL to get us all the results, but filter them down to only those that have two. Mysql> select last_name, group_concat(cell_phone separator ' | ') from friends group by last_name having count(*)=2 Let’s apply a having clause to our query and see what result it gives us. Therefore, it fits right in with the group by clause that we have been testing out so far. So what does the having keyword do? Well, it is used as a type of filter for aggregates or groups of results. You may have seen this particular keyword in various MySQL syntax you have come across. ![]() Follow the syntax in the example above and simply swap out the values you would like to use in your test environment. Now we are simply using a pipe character instead of the comma. | last_name | group_concat(cell_phone separator ' | ') | Mysql> select last_name, group_concat(cell_phone separator ' | ') from friends group by last_name ![]() If you would like to use a different delimiter, you can so so like this: Now by default, the group_concat function uses a comma to separate the items being concatenated. Notice how the cell phone numbers for couples are now concatenated together in the result set. Mysql> select last_name, group_concat(cell_phone) from friends group by last_name Let’s find the cell phone numbers of the couples in our friends table. This function allows us to stick together, or concatenate, things in a group. This is a cool little function we can make use of when working with the group by statement. The example above was looking for information on the last_name field but the aggregate function was applied to the birthday field. Additionally, the group by is applied to the field you want information on, not the field which gets the aggregate function applied to it. Be aware that the field which you use the group by on, needs to also be part of the select statement. Group by can be a little tricky at first but once you try a few tests of your own, you’ll find it to come easier. Mysql> select distinct last_name from friends The last_name column came back in alphabetical order, but there are only seven records when we know we have ten friends, why is that? Well in this case, it almost works like a distinct statement like we see here: This query grabs the youngest of our friends and groups them by last name. Mysql> select last_name, max(birthday) from friends group by last_name Let’s try a query to find the youngest of our friends and group them by their last name. You’ll need to have a play with all of these on various tables to see how you can combine them to get the results you are looking for. There are many ways to apply the group by statement when using aggregate functions like count, avg, min, max, and sum. The first column tells us the last name, and the second column gives us the number of times that last name occurs. ![]() Here we can see that there are three couples in our group of friends. Let’s see how: select last_name, count(*) from friends group by last_name We’ll need to use both the group by statement and the count aggregate function to make this happen. An example would be to find all of our friends that have the same last name. We can use the group by statement in combination with aggregate functions in MySQL to group the results by a specific column. Lastly, testing out queries using the limit, offset, and functions with order by will be reviewed. Sort orders are important as well so we will look at sorting in ascending order as well as descending order. In addition to this, we’ll look at things such as group_concat, having, and order by. They typically go together, much like peanut butter and jelly. We’ll cover features like group by and aggregate functions. As always, we’ll simply be operating on the data that we already have in the friends table we’ve been working with so far. We’re moving onward in our MySQL Journey! This episode will take a look at many useful features of the language and how you can use them. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |