SQL Aggregate Functions Tutorial With Practice Exercises
Up till now, we've been focussed on filtering rows, and ordering rows. Now, it's time to show columns some love in this lesson by covering aggregate functions, which perform operations across an entire column of data.
Here's the 5 SQL Aggregate Functions – memorize them by heart:
- adds together all the values in a particular column.
- returns the lowest value in a particular column
- returns the highest value in a particular column
- calculates the average of a group of selected values.
- counts how many rows are in a particular column.
Think of aggregate functions as your data detectives 🕵🏻♀️, helping you extract precious insights from your database, whether you're counting customers, summing up sales, or analyzing real estate property trends. By the end of this tutorial, you'll realize how SQL is good at deriving insights – not just filtering rows!
Analyzing Amazon Customer Transaction Data
To bring these concepts to life, we'll be working with the dataset which contains data on what products Amazon customers are buying, and how much they are buying them for.
Table: Sample Data
| category | product | user_id | spend | transaction_date |
|---|---|---|---|---|
| appliance | washing machine | 123 | 219.80 | 03/02/2022 11:00:00 |
| electronics | vacuum | 178 | 152.00 | 04/05/2022 10:00:00 |
| electronics | wireless headset | 156 | 249.90 | 07/08/2022 10:00:00 |
| electronics | vacuum | 145 | 189.00 | 07/15/2022 10:00:00 |
| electronics | computer mouse | 195 | 45.00 | 07/01/2022 11:00:00 |
| appliance | refrigerator | 165 | 246.00 | 12/26/2021 12:00:00 |
| appliance | refrigerator | 123 | 299.99 | 03/02/2022 11:00:00 |
| ... | ... | ... | ... | ... |
Let's dive into some practical examples of using aggregate functions to analyze this Amazon data.
Counting Number of Orders with SQL COUNT()
The function enables you to count the total number of rows in a table. We can use the function on the column as follows:
Here's an example query:
Results
| count |
|---|
| 15 |
Frequently, one of the first things we do after 'ing the data to explore it is then count how big the dataset is. We do this with the following query:
SQL COUNT Practice Exercise
Give a try by writing a query to see how big the CVS pharmacy sales dataset is.
Calculating Total Sales with SQL SUM()
The function calculates the sum of numeric values in a column. To calculate the total amount of money spent on Amazon products, we can use the function to sum the column.
Results
| sum |
|---|
| 2142.76 |
SQL SUM Practice Exercise
Practice by finding the total sales of all Pfizer manufactured medicines sold at CVS Pharmacy.
Finding the Average Price with SQL AVG()
The function computes the average value of numeric data in a column. To find the average price of an Amazon order, we can use the function on the column.
Results
| avg |
|---|
| 142.8506666666666667 |
SQL AVG Practice Exercise
Practice in the exercise below, by finding the average stock open price for Google Stock.
Finding the Minimum with SQL MIN()
The function identifies the smallest value in a column. To find the lowest priced Amazon product order, we can use the function on the column.
Results
| min |
|---|
| 7.99 |
SQL MIN Practice Exercise
Use SQL's MIN command in the practice exercise below, to find the lowest price Microsoft stock ever opened at.
Discovering the Maximum Price with SQL MAX()
The function retrieves the largest value in a column. To find the highest priced Amazon product order, we can use the function on the column.
Results
| max |
|---|
| 299.99 |
SQL MAX Practice Exercise
In the practice exercise below, use the aggregate function to find the highest price Netflix stock ever opened at.
Real-World Scenarios: Utilizing SQL Aggregate Functions for Practical Insights
1. Calculating Total Sales for Financial Analysis 👩🏻💻
- Scenario: A business wants to analyze its total sales over a specific period to assess its financial performance.
- Use Case: The function can be used to calculate the total revenue generated from sales during the chosen time frame, aiding in financial planning and analysis.
2. Average Ratings for Product Reviews 🛍️
- Scenario: An e-commerce platform wants to showcase the average rating of products based on customer reviews.
- Use Case: The function can calculate the average rating of products, helping customers make informed purchasing decisions.
3. Identifying Most Active Users📱
- Scenario: A social networking site wants to recognize its most active users based on the number of posts they've made.
- Use Case: The function can tally the number of posts for each user, highlighting the most prolific contributors.
4. Finding Lowest and Highest Prices ✈️
- Scenario: A travel website wants to display the cheapest and most expensive flight options for users.
- Use Case: The and functions can identify the lowest and highest prices for flights, allowing users to quickly compare options.
5. Tracking Student Performance in Education 📚
- Scenario: An educational institution wants to track student performance by calculating average scores and identifying top performers.
- Use Case: The function can compute the average scores of students, while the function can help find the highest scorers.
Summing It All Up
To take your analysis to the next level, we won't just use aggregate functions like SUM or AVG on an entire dataset – we'll analyze sub-groups of data using the GROUP BY command!
Next Lesson
SQL GROUP BY 📊