Explains how to use the main text-grouping function, and gives examples of when student might want to use it
- [Instructor] So far when dealing with grouped results, numeric functions have had all the fun. I want to introduce you to one string function that you can run on grouped data, and this is called group_concat. It's a rare case where MySQL offers you something that Microsoft doesn't. Oracle does though with something called listagg, and PostGreSQL does it twice over with array_agg and string_agg. It's all very well counting or summing your data, but sometimes you need to see the full contents of the original cells.
Let's say that you want to call all of your customers one city at a time. So we can return the phone numbers of the customers against their district. And we'll do that by selecting distinct district which is this first column, and then group_concat(phone) from address group by district. And this is what we're given. One of the ways in which this is terribly useful is if you are trying to create a data array for another language altogether from your database.
And I'm gonna ask the database software to separate by semicolon, and notice that I've put the semicolon inside of single quotation marks there. And if we run that we can see, you may notice that the order has changed in some cases, and there's a semicolon instead of a comma there now. In PostGreSQL and Oracle, you would set the separator or delimiter as an optional section argument within those functions, rather than typing it out long hand as we do here.
Join Emma Saunders as she shows you how to design and write simple SQL queries for data reporting and analysis. Review the different types of SQL, and then learn how to filter, group, and sort data, using built-in SQL functions to format or calculate results. Learn a bit about data types and database design. Discover how to perform more complex queries, such as joining data together from different database tables. Last but not least, Emma shows how to save your queries as views, so you can run them again and again.
- Using different versions of SQL
- Retrieving data with SELECT statements
- Filtering and sorting your results
- Transforming results with built-in SQL functions
- Grouping SQL results
- Merging data from multiple tables
- Identifying data types, and how to make sense of your database design
- Saving SQL queries