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:
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!
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
Let's dive into some practical examples of using aggregate functions to analyze this Amazon data.
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:
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:
Give a try by writing a query to see how big the CVS pharmacy sales dataset is.
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.
Practice by finding the total sales of all Pfizer manufactured medicines sold at CVS Pharmacy.
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.
Practice in the exercise below, by finding the average stock open price for Google Stock.
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.
Use SQL's MIN command in the practice exercise below, to find the lowest price Microsoft stock ever opened at.
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.
In the practice exercise below, use the aggregate function to find the highest price Netflix stock ever opened at.
1. Calculating Total Sales for Financial Analysis 👩🏻💻
2. Average Ratings for Product Reviews 🛍️
3. Identifying Most Active Users📱
4. Finding Lowest and Highest Prices ✈️
5. Tracking Student Performance in Education 📚
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!
SQL GROUP BY 📊