Big SQL Energy Beginner Course: Lesson 22 Group By w/ Aggregate Functions

Jan 22, 2025

Got BDE?⚡️

Hi BDEs! Welcome to Lesson 22 of the free Big SQL Energy Beginner Course⚡️ Today we’re learning about Group By w/ 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 22: Group By w/ Aggregate Functions

🎥 Video

Not only is Group By an essential part of writing SQL queries, but it’s also the foundation of A LOT of interview questions. Understanding Group By will unlock so much for you when writing queries because it allows us to group our data into different segments based on 1 or more columns. Partitioning our data into different groups based on their values allows us to perform calculations within each group instead of just for the entire dataset.

My favorite analogy for group by is sorting candy. Imagine we separate a pile of candies by color and count the number of candies in each one. We have a pile of blue candies, green candies, yellow candies, etc. and count each pile within each color. That’s exactly how we use group by in SQL. We can group our data based on certain attributes and then perform calculations such as our aggregate functions (sum, average, min, count) within each category.

Group by ALWAYS goes after select, from, & where (if you have a where) in your query. And when you use group by and aggregate functions, all columns in the select statement must be either in the group by clause or aggregated. Otherwise you’ll get an error! This error was so frustrating for me as a new data analyst, so I’m happy to share that tidbit with you now.

Here’s how we can use group by to count the number of orders and sum the total revenue for each customer:

select 
    customer_id, 
    count(*) as num_orders,
    SUM(price * quantity) AS total_revenue
FROM 
    orders 
GROUP BY 
    customer_id;

We can also segment and group by multiple columns by adding them to group by. Be sure you also add them to the select statement as well. It’s best practice to have all your group by columns on the far left side of the output (top of select statement).

select 
    customer_id, 
    payment_method,
    count(*) as num_orders,
    SUM(price * quantity) AS total_revenue
FROM 
    orders 
GROUP BY 
    customer_id, payment_method;

You can also use the select statement column numbers instead of the column names to simplify your query and reduce clutter:

select 
    customer_id, 
    payment_method,
    count(*) as num_orders,
    SUM(price * quantity) AS total_revenue
FROM 
    orders 
GROUP BY 
    1, 2;

In the next lesson, we’ll learn about HAVING and compare its use case to GROUP BY! 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!