Creating groups of data

1. Creating groups of data

In this video, you'll learn how to write more advanced grouping queries using GROUP BY.

2. Grouping data

Up until now, all group functions have treated the table as one large group of information. At times, however, you need to divide the table of information into smaller groups. This can be done by using the GROUP BY clause.

3. Group information

Let's look at the Composer and Milliseconds columns of the Track table. Composers can write multiple songs. As you can see Vivaldi has composed one track, Pearl Jam two, Jimmy Page five, Carlos Santana three, and so on. But what if you want to know aggregate information about the songs written by each composer? For example, what is the average track length of songs written by each composer? You can use the GROUP BY clause to divide the rows in a table into groups. You can then use the group functions to return summary information for each group.

4. GROUP BY

Let's look at an example. The SELECT clause specifies the columns to be retrieved. In this case, the composer and the average of all track lengths, in milliseconds. The GROUP BY clause specifies that the rows should be grouped by composer. So the AVG() function that is applied to the milliseconds column calculates the average track length for each composer. The output shows only one row for every composer followed by the summary information.

5. GROUP BY and WHERE

You can use WHERE to exclude rows before dividing them into groups. In this query, we filter on rows with a genre id equal to 1. The remaining rows are grouped by composer and the composer together with the average track length are displayed.

6. GROUP BY and ORDER BY

You can also sort by using ORDER BY. Let's order the results by average track length. To do this, you can use the same expression you used in the SELECT statement, the average of milliseconds in this case, in the ORDER BY. As you can see, the composer with the lowest average track length is displayed first.

7. GROUP BY and ORDER BY

You can achieve the same result by using numbers in the ORDER BY clause. Oracle SQL allows you to sort the results based on the ordinal positions of columns that appear in the SELECT statement. In this example, 1 means the Composer column and 2 means the AVG(Milliseconds) column.

8. GROUP BY and ORDER BY

Or you can use the column alias in the ORDER BY clause as is shown here.

9. Guidelines

There are some things you need to pay attention to when working with the GROUP BY clause. Any column or expression in the SELECT statement that is not an aggregate function must be in the GROUP BY clause. In this query, you can see that the UnitPrice column is not included in the GROUP BY. Running this query will throw an error. Since you are grouping by composer, it's not possible to keep information on a row-level. In this case the unit price is available for every track and will need to be aggregated across all rows related to the same composer. You can correct the error

10. Guidelines

by using an aggregate function with UnitPrice.

11. Guidelines

In contrast, expressions that are specified in the GROUP BY do not have to be included in the SELECT statement. For example, this query displays the average track length for each composer without displaying the respective composer. Without the composer, however, the results do not look meaningful.

12. Multiple columns

Sometimes you need to see results for groups within groups, for example cities in countries. You can do this by listing more than one GROUP BY column. In this query, results are grouped by country and then by city. The number of customers are counted for each group and subgroup. The default sort order of the results is determined by the order of the columns in the GROUP BY clause.

13. Let's practice!

Let's do some grouping!