Summary
This chapter explains how to group rows in a result set using the GROUP BY clause to perform aggregate operations (e.g., COUNT, AVG) on grouped data and how to assign aliases to result columns for clearer output.

General domain of usage
Relational database querying and reporting

Let's understand what "grouping data" means using a simple example of an employees table:

## Grouping Data

We have a **task** to **find out the number of employees in each department.** To do this, we will group the data by the `department` column and use aggregation with the `COUNT(*)` function. 
 
Here's what the implementation will look like:

SELECT department, COUNT(*) AS number_of_employees
FROM employees
GROUP BY department

So, as you can see, **the syntax** for grouping data looks like this:

```sql
SELECT column1, AGG_FUNC(column2)
FROM table
GROUP BY column1
```

`AGG_FUNC` means aggregate functions like `MAX`, `MIN`, `COUNT`, etc.

Note

This syntax exists to **find certain values using aggregate functions in specific columns**. 

Let's consider another example: we've been tasked with **finding the department with the highest average salary.**

To retrieve such data, we need to **group** the data by the `department` column and then use the `AVG()` function to calculate the average salary:

SELECT department, AVG(salary) as average_salary
FROM employees
GROUP BY department

In this part of the section, we will work with the **Montreal Metro system database**, which contains the `metro_travel_time` table. 



This table will contain information about the **station line**(`line_name`), its **name**(`station_name`), and **the amount of time** it takes for a train **to travel from one station to the next one**(`time_to_next_station`).

Here is what this **table** looks like and the **data preview** in it:


As you can see, this is **not a complex table**. Let's think about where we can **use grouping** here.

The most obvious option is **grouping by the colors of metro lines**. That means we can aggregate the data, grouping it by the color of the metro line. 




## Alias

In the assignments, you’ll often use a concept called an **alias**. An alias is essentially a "nickname" for a column you retrieve with a `SELECT` statement. It’s specified using the following syntax:

```sql
SELECT column AS alias
```

An alias only affects how the column appears in the response. 

For example, instead of `MAX(time)`, the column could be called `max_time` if you assign that alias. This makes the output more readable and clear.

This course is perfect for those who already have a basic understanding of SQL and want to delve into more advanced concepts to craft more powerful queries. Throughout the course, you will become familiar with data grouping and filtering grouped data. You will also learn how to work with multiple tables simultaneously, including how to combine them. Additionally, you will explore different types of table joins and how to apply them in practice.

GROUP BY Clause

Grouping Data

Alias

Brief Instructions

解答