Big SQL Energy Beginner Course: Lesson 15 Aggregate Functions

Jan 15, 2025

Got BDE?⚡️

Hi BDEs! Welcome to Lesson 15 of the free Big SQL Energy Beginner Course⚡️ Today we’re learning about Aggregate Functions in SQL!

Return to Big SQL Energy Beginner homepage here to access the other lessons:

Return to Homepage 📚

If you’re new here:

💁🏽‍♀️ Who Am I?

I’m Jess Ramos, the founder of Big Data Energy and the creator of the BEST SQL course and community: Big SQL Energy⚡️. Check me out on socials: 🔗LinkedIn, 🔗Instagram, 🔗YouTube, and 🔗TikTok. And of course subscribe to my 🔗newsletter here for all my upcoming lessons and updates— all for free!

⚡️ What is Big SQL Energy?

🔗Big SQL Energy Intermediate is an intermediate SQL course designed to solve real-world business problems hands-on using realistic data and a modern tech stack. You’ll walk away with 2 SQL portfolio projects, portfolio building guidance, access to the Big Data Energy Discord community, and lots of confidence for you upcoming coding interviews! You also get access to all monthly events including masterclasses, office hours, and guest speakers. This course is all of the most important things I’ve learned on the job as a Senior Data Analyst in tech who grew from a $72K salary to over a $150K salary. And now I’m sharing it with YOU! It’s more than just another course— it’s a challenging program designed to upskill your SQL to the intermediate level, get ready to ace your live coding interviews, and build connections in the data community through the Discord and events.

JOIN now (pun intended 😉)

Lesson 15: Aggregate Functions

🎥 Video

Aggregate functions are probably one of the most important skills you can have when first learning SQL. They’re the way of combining and summarizing data from many, many noisy rows into metrics, insights, and digestible values. Think about a large spreadsheet. What’s one of the first things you might want to do? Maybe you’d want to count the number of rows, find the sum of a column, or fine the minimum and maximum values. This exactly what we can do with aggregate functions.

Aggregate functions can be great for performing descriptive statistics on a dataset, and they are pure MAGIC when paired with a GROUP BY or HAVING clause in SQL. Don’t worry, those lessons are later on in this beginner course, but just keep in mind how versatile aggregate functions are.

Of course basic aggregate functions are really useful on their own, but I do want to point out that they’re also something that can be built on to produce advanced SQL work such as window functions, pivoting data in SQL using CASE, and even in CTES— which are all taught in-depth in my 🔗intermediate course, so head on over there to learn advanced use cases for aggregate functions.

There are a few main aggregate functions that I use the most, but there are of course some others as well.

Count(): counts all the non-null values in a column. Note: count(*) will count rows, but we’ll learn that in the next lesson, so ignore this for now.

select
   count(order_id) as order_count
from 
   orders

Sum(): sums numeric values in a column.

select
   sum(price) as total_price
from 
   orders

Avg(): Calculates the average (mean) of numeric values in a column.

select
   avg(price) as avg_price
from 
   orders

Min(): Finds the minimum value in a column.

select
   min(price) as min_price
from 
   orders

Max(): Finds the maximum value in a column.

select
   max(price) as max_price
from 
   orders

Bonus: Median(): Finds the median value of a column. Note: Not all SQL environments and dialects will support this function.

select
   median(price) as median_price
from 
   orders

Not only can we produce basic descriptive statistics using aggregate functions, but we can also wrap aggregate functions around other metrics and calculations we’ve created. For example, we can fine the sum() and avg() of the total_revenue calculation created by multiplying price and quantity (using arithmetic operators):

select
   price * quantity as total_order_revenue
from 
   orders
;

select
   sum(price * quantity) as total_revenue,
   avg(price * quantity) as avg_revenue
from 
   orders
;

And those are basic aggregate functions— now go GET TO PRACTICING!

If you’ve enjoyed the free BEGINNER Big SQL Energy course, grab my intermediate course. There are no deadlines or timelines for this course— you start and take as long as you want. It will take your beginner skills to the next level and get you ready to ace your interviews. DM me or email me at courses@bdeanalytics.com if you have questions on the intermediate course!