SQL aggregations – Lesson 4

Summarizing data with confidence using count, sum, avg, min, and max

Photo by Nancy Hughes on Unsplash

👋🏽 Hey, it’s Ismail. Welcome to data nomads lab newsletter on learning data analytics, career growth, networking, building portfolios, and interview skills to break into tech role as a high-performer.

From rows to insights

In Lesson 3, we learned how to join tables together using SQL joins. That gave us the power to connect different parts of the Chinook database—artists to albums, customers to purchases, and tracks to invoices.

If you missed that one, you can go back and read the full SQL Joins lesson here

SQL Joins – Lesson 3

But here’s the thing: sometimes we don’t want to look at individual rows.
We want to look at the big picture.

• How many tracks are there in total?

• What’s the average price of a track?

• How much revenue did we generate from invoices?

• Which album has the most songs?

To answer questions like these, we use SQL aggregation functions.

Today, we’ll cover:
✅ COUNT() – how many?
✅ SUM() - total value
✅ AVG() - average
✅ MIN() / MAX() – lowest and highest
✅ GROUP BY – grouping rows for summaries
✅ Real-world examples using the Chinook database

COUNT(): how many rows?

The COUNT() function tells you how many rows meet a condition—or exist at all.

Question:
How many tracks are in the database?

SELECT COUNT(*) AS TotalTracks  
FROM Track;

✅ This gives you one number: the total number of rows in the Track table.

SUM(): total value

SUM() adds up the values in a numeric column.

Question:
How much total revenue has the business made?

SELECT SUM(Total) AS TotalRevenue  
FROM Invoice;

✅ This adds up every Total value from the Invoice table, giving us the total revenue across all invoices.

AVG(): average value

AVG() calculates the average (mean) value.

Question:
What is the average price per invoice?

SELECT AVG(Total) AS AverageInvoiceValue  
FROM Invoice;

You can also apply AVG() to things like track length or price.

MIN() / MAX(): smallest and largest

MIN() returns the lowest value, and MAX() returns the highest.

Question:
What’s the longest track in the database (in milliseconds)?

SELECT MAX(Milliseconds) AS LongestTrack   
FROM Track;

Question:
What’s the shortest track?

SELECT MIN(Milliseconds) AS ShortestTrack  
FROM Track;

GROUP BY: breaking data into categories

Now this is where aggregations get powerful.
GROUP BY allows you to calculate things for each group—not just a total.

Question:
How many tracks are in each album?

SELECT AlbumId, COUNT(*) AS TrackCount  
FROM Track  
GROUP BY AlbumId;

Each row in the result tells you how many tracks belong to each album.

You can also join tables to make the results more meaningful.

Question:
How many tracks does each artist have?

SELECT Artist.Name, COUNT(*) AS TotalTracks  
FROM Artist  
JOIN Album ON Artist.ArtistId = Album.ArtistId  
JOIN Track ON Album.AlbumId = Track.AlbumId  
GROUP BY Artist.Name  
ORDER BY TotalTracks DESC;

✅ This joins three tables to count tracks per artist and orders them from most to fewest.

Combining WHERE+ GROUP BY

Question:
How many invoices did we have in each country, but only for countries with at least 5 invoices?

SELECT BillingCountry, COUNT(*) AS InvoiceCount  
FROM Invoice  
GROUP BY BillingCountry  
HAVING COUNT(*) >= 5  
ORDER BY InvoiceCount DESC;

👉 HAVING is like WHERE but works after grouping.
We use HAVING to filter groups, not individual rows.

Real-world applications of aggregations

These functions help us answer everyday business questions:

• How many users signed up last month?

• What is the average customer order size?

• Which products are selling the most?

• Who are our top-spending customers?

And just like that, raw data becomes a story.

Lesson recap

✅ COUNT() – number of rows
✅ SUM() – total value
✅ AVG() – average value
✅ MIN() / MAX() – smallest/largest
✅ GROUP BY – summarize by category
✅ HAVING – filter groups after grouping

You now know how to take thousands of rows and boil them down into key insights.

Next lesson: SQL Subqueries – queries inside queries

In our next lesson, we will explore SQL subqueries—how to write queries inside other queries to answer more complex questions.

You will learn how to:

• Filter results based on a summary

• Compare one row to group statistics

• Write cleaner, more readable SQL

Stay tuned for more examples and walkthroughs!

If this lesson helped you, please share and support Data Nomads Lab on Substack. Your support helps keep this content free and accessible to learners around the world.

Until next time—happy querying!