Course Content
Intermediate SQL
Intermediate SQL
GROUP BY
Hi there! 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:
Now, let's imagine 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:
Note
AGG_FUNC
means aggregate functions likeMAX
,MIN
,COUNT
, etc.
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
Note
Please note that we will not be using this table in assignments; the
employees
table will be used solely for demonstrating syntax examples and their usage.
In this course, 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.
Now, let's practice grouping by completing a task.
Task
Your task is to find the longest time until the next station on each line. This will allow us to determine the longest travel time between stations for each metro line. To do this, use the MAX()
function and alias it as max_time
, grouping the data by the line_name
column.
Once you've completed this task, click the button below the code to check your solution.
Task
Your task is to find the longest time until the next station on each line. This will allow us to determine the longest travel time between stations for each metro line. To do this, use the MAX()
function and alias it as max_time
, grouping the data by the line_name
column.
Once you've completed this task, click the button below the code to check your solution.
Everything was clear?
GROUP BY
Hi there! 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:
Now, let's imagine 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:
Note
AGG_FUNC
means aggregate functions likeMAX
,MIN
,COUNT
, etc.
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
Note
Please note that we will not be using this table in assignments; the
employees
table will be used solely for demonstrating syntax examples and their usage.
In this course, 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.
Now, let's practice grouping by completing a task.
Task
Your task is to find the longest time until the next station on each line. This will allow us to determine the longest travel time between stations for each metro line. To do this, use the MAX()
function and alias it as max_time
, grouping the data by the line_name
column.
Once you've completed this task, click the button below the code to check your solution.
Task
Your task is to find the longest time until the next station on each line. This will allow us to determine the longest travel time between stations for each metro line. To do this, use the MAX()
function and alias it as max_time
, grouping the data by the line_name
column.
Once you've completed this task, click the button below the code to check your solution.
Everything was clear?
GROUP BY
Hi there! 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:
Now, let's imagine 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:
Note
AGG_FUNC
means aggregate functions likeMAX
,MIN
,COUNT
, etc.
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
Note
Please note that we will not be using this table in assignments; the
employees
table will be used solely for demonstrating syntax examples and their usage.
In this course, 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.
Now, let's practice grouping by completing a task.
Task
Your task is to find the longest time until the next station on each line. This will allow us to determine the longest travel time between stations for each metro line. To do this, use the MAX()
function and alias it as max_time
, grouping the data by the line_name
column.
Once you've completed this task, click the button below the code to check your solution.
Task
Your task is to find the longest time until the next station on each line. This will allow us to determine the longest travel time between stations for each metro line. To do this, use the MAX()
function and alias it as max_time
, grouping the data by the line_name
column.
Once you've completed this task, click the button below the code to check your solution.
Everything was clear?
Hi there! 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:
Now, let's imagine 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:
Note
AGG_FUNC
means aggregate functions likeMAX
,MIN
,COUNT
, etc.
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
Note
Please note that we will not be using this table in assignments; the
employees
table will be used solely for demonstrating syntax examples and their usage.
In this course, 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.
Now, let's practice grouping by completing a task.
Task
Your task is to find the longest time until the next station on each line. This will allow us to determine the longest travel time between stations for each metro line. To do this, use the MAX()
function and alias it as max_time
, grouping the data by the line_name
column.
Once you've completed this task, click the button below the code to check your solution.