![]() 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.
0 Comments
Leave a Reply. |