Group functions

1. Group functions

Welcome to chapter 2. In this chapter, you will learn how to use group functions to summarize data and gain useful insights.

2. Aggregating data

To get an understanding of a new dataset, it's common to aggregate data. This could mean counting the number of observations, calculating the sum, or looking for the maximum value in a column.

3. Group functions

Group functions operate on multiple rows and return one result for the entire group. There are different group functions you can use. Some of them are: sum, average, median, min, max, and count. Let's take a closer look at each of these.

4. SUM

How long would it take to play all songs that eSymphony offers? You can calculate this by using the SUM function in the SELECT clause. Between parentheses you need to include the column name, in our case, Milliseconds. That's a lot of milliseconds!

5. AVG, MEDIAN

Similarly, to get a sense of the average song length, you can calculate the mean and median of the Milliseconds column. The average function calculates the mean.

6. MIN, MAX

The MIN and MAX functions can be used to calculate the minimum and maximum values, respectively. Looks like the shortest track available lasts only one second.

7. COUNT

The COUNT function has three formats. COUNT star returns the number of rows in a table. This includes duplicate rows and rows containing null values in any of the columns. Looks like there are 3503 rows in the Track table. If a WHERE clause is included in the SELECT statement, COUNT star returns the number of rows that satisfy the condition in the WHERE clause. In contrast, COUNT Milliseconds returns only the number of non-null values that are in the column. Once again, the output is 3503. This means there are no null values in this column. COUNT DISTINCT Milliseconds returns the number of unique, non-null values that are in the column. This time the output is 3080. We can conclude there are songs that have the exact same duration.

8. Column aliases

You may have noticed before that when you display the result of a query, the expression used in the SELECT statement is used as the column heading. This heading may not be very descriptive and difficult to understand. You can change a column heading by using a column alias. Specify the alias after the column in the SELECT clause. The AS keyword can be left out. By default, alias headings appear in uppercase. If the alias contains spaces or special characters, or if it is case-sensitive, you need to enclose the alias in double quotation marks. Single quotation marks will lead to an error.

9. Data types

Data types define what type of data a column can contain. Not all group functions can be used with every data type. While AVG() and SUM() only work on numeric data, MAX(), MIN(), and COUNT() can be used for numeric, character, and date data types. We'll look at the different data types in Oracle SQL in more detail in Chapter 4 but let's have a quick overview.

10. Character

Text data types like CHAR and VARCHAR2 allow for a fixed or varying number of characters and string data, like the Name column.

11. Numeric

Numeric data types like NUMBER allow you to store zero negative and positive numbers. An example of this is the Millisecond column.

12. Date

Date data types like DATE and DATETIME contain information about the date and time. An example is the BirthDate column of the Employee table.

13. Let's practice!

Let's start aggregating some data!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.