Summary  
This chapter covers using the SQL GROUP BY clause to group rows by specified columns, apply aggregate functions like COUNT and AVG, and use aliases to rename result columns for clarity.  

General domain of usage  
Transportation system performance analysis

Welcome to the **Intermediate SQL** course! 


In the first section, we're diving into how we can **group and aggregate data** within our tables. 


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.

Questo corso è perfetto per coloro che hanno già una comprensione di base di SQL e vogliono approfondire concetti più avanzati per creare query più potenti. Durante il corso, diventerai familiare con il raggruppamento dei dati e il filtraggio dei dati raggruppati. Imparerai anche a lavorare con più tabelle contemporaneamente, incluso come combinarle. Inoltre, esplorerai diversi tipi di join di tabelle e come applicarli nella pratica.

In questa sezione, verrà illustrato come raggruppare e gestire i dati in modo efficace. Verrà introdotto l'operatore GROUP BY per il raggruppamento dei dati. Inoltre, verrà spiegato come filtrare i dati raggruppati utilizzando l'operatore HAVING.

Verranno trattate le sottoquery annidate, come crearle e come utilizzarle in modo efficace.

Qui imparerai come combinare più tabelle, consentendoti di lavorare con una tabella unificata e semplificare la creazione delle query.

Impara i fondamenti del Linguaggio di Definizione dei Dati (DDL) e del Linguaggio di Manipolazione dei Dati (DML) in SQL, inclusi come creare, modificare e eliminare oggetti del database, nonché come inserire, aggiornare ed eliminare dati all'interno delle tabelle.

GROUP BY Clause

Grouping Data

Alias

Brief Instructions

Soluzione