![]() ![]() 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. about GROUPING SETS and how they influence the GROUP BY operation, or about the logical order of SQL operations (as opposed to the lexical order of operations).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. ![]() Use DISTINCT to remove duplicates with more than one GROUP BY columnĪnother case, of course, is this one: SELECT DISTINCT actors, COUNT(*)įROM (VALUES('a', 1), ('a', 1), ('b', 1), ('b', 2)) t(actors, id)įor more details, I've written some blog posts, e.g. In a less far-fetched example, you might be interested in the DISTINCT aggregated values, such as, how many different duplicate numbers of actors are there? SELECT DISTINCT COUNT(*) In a completely silly example using GROUPING SETS() in general (or the special grouping sets ROLLUP() or CUBE() in particular), you could use DISTINCT in order to remove the duplicate values produced by the grouping sets again: SELECT DISTINCT actorsįROM (VALUES('a'), ('a'), ('b'), ('b')) t(actors)īut why, apart from making an academic point, would you do that? Use DISTINCT to find unique aggregate function values ![]() Use DISTINCT to remove duplicate GROUPING SETS from the GROUP BY clause ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |