SQL: Aggregate Functions and Grouping Results

Name: SQL: Aggregate Functions and Grouping Results
Author: Eric Grose

Discover how and when to join data tables using SQL

Preview the first lesson free — get full access to all 6 lessons.

Course: On-Demand

Hello, my name is Eric, and in these lessons you'll learn the fundamentals of grouproom results in the SQL query. This lesson will cover the basic syntax to group data in the SQL query. Learning how to group data opens up a whole new universe of opportunities to analyze and summarize data. The techniques you will learn will give you the skills to quickly understand and draw conclusions on large data sets. In order to group our results in the SQL query, we're going to add a new function called groupby to our query structure. This function is used to group data based on one or more columns in a table. If we look at our generic syntax here, we can see a few more additions as well. First, we see in the select line that we now have an aggregate function that wraps around another column with parentheses. The aggregate functions that are available to us in SQL are count, average, sum, min, and max. This type of analysis is very similar to using a pivot table in Excel. When grouping results, we'll select a column and associate value we want to summarize. Then, all we have to do is refer to the column that does not have the aggregate function being applied to it. Let's take a look at our generic syntax for the count function that will return the number of rows for each group in column one. This is a handy function when you're first starting to analyze a data table. Also, if this is confusing, please don't worry as we'll go over each of these functions with sample data in the preceding lessons. Next, we have the average function, which will produce the average value for each of the categorical values in column one. We can use the sum function to calculate the total for each of the groups specified in column one. If you want to find the smallest value for the groups listed in column one, we could use the min function to retrieve those results. Conversely, if we're interested in determining the maximum value for each group, we could use the max aggregate function in SQL. Last, we can use the halving clause in conjunction with the group by function to add conditional statements to our SQL query. The halving clause works almost identical to the where clause in SQL, and that allows you to only return results that meet the criteria that you have defined. To be able to truly understand these functions, let's create a new table with more rows and columns for which we can use as categories. As we can see in our sales data set, we have a branch, city, customer type, gender, and product line. These columns are really good demographic information that we can use to categorize and summarize our data, as well as we have quantity, total, and ratings, which can be used to calculate and summarize some of our data. So if we go back to our SQL window, we can use the create table function to create a table called sales, and I've created all the columns and their associated data types to match our CSV file. So go ahead and run this by pressing Control-Enter, and we see it has created a new sales table. But when we click on the sales table, we'll see that there are no rows in here yet. Let's go up to the Import tab, we'll choose our sales file, leave the character set of UTF-8, we're going to switch this to a 1 to skip over the column headers, make sure our format is CSV, and we can leave the default values for these cells. Scroll down to the bottom and hit Import, and we can see that now a thousand rows have been loaded into our sales table. So if we click on the sales table, we see the rows that we are expecting. Thanks for watching, stay tuned for the next lesson where we continue our analysis using the average aggregate function.

Beginner Provider : Eric Grose 6 Lessons · 28m in English

Course Description

Joining data tables in SQL is a crucial skill for any data analyst or data scientist. It enables you to combine data from multiple sources and provides you with a more comprehensive view of your data. When working with multiple tables, joining allows you to extract meaningful insights that may not have been possible otherwise.

In these lessons, you’ll learn to understand the types of joins available in SQL and when to use them. We’ll cover the most commonly used types of joins, including inner, outer, left, right, and full outer joins. Each type of join has its specific use cases and is helpful in different scenarios. By the end of these lessons, you will have a deep understanding of how to join data tables and how to construct join queries, allowing you to navigate any database and combine any available data table for analysis.

What You'll Learn

Explain the principles of data joins and their appropriate uses
Construct SQL queries using left and right joins correctly
Recognize scenarios where an inner join fits the data being analyzed
Identify opportunities to perform an outer join
Apply aggregate functions including AVG, COUNT, SUM, and MIN/MAX
Group data and filter grouped results using the HAVING clause

Key Takeaways

Joining data tables in SQL lets you combine data from multiple sources for a more comprehensive view of your data.
SQL offers several join types, including inner, outer, left, right, and full outer joins, each suited to specific use cases.
Joining tables enables you to extract meaningful insights that may not have been possible from a single table.
The course covers grouping data along with aggregate functions such as AVG, COUNT, SUM, and MIN/MAX, plus the HAVING clause.
Mastering joins allows you to navigate any database and combine available data tables for analysis.

Frequently Asked Questions

Who is this course for?

It is designed for data analysts and data scientists, for whom joining data tables in SQL is described as a crucial skill.

What types of joins does the course cover?

It covers the most commonly used join types, including inner, outer, left, right, and full outer joins, along with their specific use cases.

What aggregate functions and clauses are taught?

The lessons cover grouping data fundamentals and aggregate functions including AVG, COUNT, SUM, and MIN/MAX, as well as the HAVING clause.

What will I be able to do by the end of the lessons?

You will understand how to join data tables and construct join queries, allowing you to navigate any database and combine any available data table for analysis.

What skills does this course build?

It builds skills in joining processes, relational operators, external tables, and SQL services.