Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.Learn GROUP BY Clause | Grouping
Intermediate SQL
course content

Course Content

Intermediate SQL

Intermediate SQL

1. Grouping
GROUP BY ClauseOrder of StatementsChallenge: Total Ride Time for Each Subway LineAverage Student's Grade ChallengeSearching for the Top Math Students ChallengeHAVING ClauseSeveral Aggregate Functions in the QueryMore Complex Filtering
2. Nested Subqueries
Nested QueriesChallenge: Non-tech Department EmployeesUsing Inner Subqueries in the FROM SectionChallenge: Employees With More Than Average SalaryUNION ClauseChallenge: Combining Table Results
3. Joining Tables
JOIN 2 TablesChallenge: Get Category By Product PriceChallenge: Get Minimum Price By CategoryChallenge: Get Product Amount By CategoryLEFT, RIGHT and INNER JOINsChallenge: Table JoinChallenge: FULL JOIN Usage
4. DDL and DML in SQL
CREATE and ConstraintsALTER and INSERT OperationsDROP and TRUNCATEUPDATE and DELETE

book
GROUP BY Clause

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:


              
123

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

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

sql

Note

AGG_FUNC means aggregate functions like MAX, 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:


              
123

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

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

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.

Task

Swipe to start coding

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.

Brief Instructions

  • Retrieve the line_name column and the maximum value of the time_to_next_station column.
  • Add the alias max_time for the maximum value.
  • Group the data by the line_name column.

Once you've completed this task, click the button below the code to check your solution.

Solution

Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
Everything was clear?

How can we improve it?

Thanks for your feedback!

SectionΒ 1. ChapterΒ 1
toggle bottom row

book
GROUP BY Clause

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:


              
123

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

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

sql

Note

AGG_FUNC means aggregate functions like MAX, 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:


              
123

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

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

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.

Task

Swipe to start coding

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.

Brief Instructions

  • Retrieve the line_name column and the maximum value of the time_to_next_station column.
  • Add the alias max_time for the maximum value.
  • Group the data by the line_name column.

Once you've completed this task, click the button below the code to check your solution.

Solution

Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
Everything was clear?

How can we improve it?

Thanks for your feedback!

SectionΒ 1. ChapterΒ 1
Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
We're sorry to hear that something went wrong. What happened?
some-alt