Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 5

Summarizing Grouped Data with Aggregate Functions and GROUP BY

Recommended resources

Welcome back. In our last lesson, you learned how to retrieve, filter, and sort individual rows of data using the fundamental SQL clauses SELECT, WHERE, and ORDER BY. You can now ask a database for specific records and organize them neatly.

This lesson takes you a step further, from viewing data to summarizing it. Instead of just listing rows, you'll learn how to calculate totals, averages, counts, and other summary statistics. This is accomplished using aggregate functions combined with the powerful GROUP BY clause. By the end of this session, you'll be able to answer more complex questions, such as "What was the total box office revenue per year?" or "How many employees are in each role?". These are foundational skills for any form of data analysis and reporting, moving you from a data consumer to a data interpreter.

From Individual Rows to Summary Values

So far, your queries have returned one or more rows that already existed in the table. Aggregate functions are different: they perform a calculation across many rows and return a single, summary value.

For example, you could ask for the total number of movies, the average box office gross, or the release year of the earliest film. SQL provides a standard set of functions for these tasks.

The following video provides a clear conceptual overview of the five most common aggregate functions: COUNT, SUM, AVG, MAX, and MIN.

SQL Aggregate Functions | COUNT, SUM, AVG, MAX, MIN | #SQL Course 21

Watch the first two minutes of this video from the "Data with Baraa" channel to understand the purpose of each main aggregate function.

Focus on the conceptual explanations from the beginning. The presenter uses simple diagrams to illustrate how each function collapses multiple rows into a single summary value.

To complement the video, here is a quick reference guide that defines these functions. It's a good idea to keep these in mind as we proceed.

SQL Lesson 10: Queries with aggregates (Pt. 1)

This table from SQLBolt provides a concise summary of the five key aggregate functions.

Please review the table under the heading Common aggregate functions. Pay attention to the distinction between COUNT(*) (which counts all rows) and COUNT(column) (which counts non-null values in a specific column).

Applying Aggregates to the Whole Table

The simplest way to use an aggregate function is to apply it to an entire table to get a single summary result. Let's see how this works in a real-world data engineering context using Google BigQuery, the analytics warehouse we will be studying extensively.

The next video demonstrates these functions on a large public dataset of NYC taxi trips.

Want to Master SQL GROUP BY? Watch This Now

Watch this short clip from "Analytics with Adam" to see how aggregates are used in a query.

Watch the section from the demonstration. Notice how running a query with only aggregate functions in the SELECT list produces a single row of results summarizing the entire dataset.

This is useful for getting a high-level overview, but the real power of aggregation comes when you combine it with grouping.

Grouping Data for Deeper Insights

What if you don't want the total count of all movies, but the count of movies for each year? Or the average ticket price for each payment type? This is where the GROUP BY clause comes in.

The GROUP BY clause partitions rows into groups based on the values in one or more columns. The aggregate functions are then applied to each of these groups independently.

The process works like this:

  1. Group: SQL first organizes all rows into buckets, where each bucket corresponds to a unique value in the column specified in the GROUP BY clause.
  2. Aggregate: It then applies the aggregate function(s) (e.g., COUNT, SUM) to the rows within each bucket.
  3. Return: Finally, it returns one summary row for each bucket.

Let's read a tutorial that explains this core concept.

SQL GROUP BY Clause | Intermediate SQL | ThoughtSpot

This article from ThoughtSpot's SQL tutorial provides a clear explanation of how GROUP BY works.

Read the section titled The SQL GROUP BY clause. This part explains how grouping allows you to aggregate parts of a table independently. Notice the example that groups by year and then by both year and month.

A crucial rule to remember when using GROUP BY is: any column in your SELECT statement that is not an aggregate function must be listed in your GROUP BY clause. This ensures that SQL knows how to generate the summary row for each group.

Putting It All Together: A Complete Aggregation Query

A typical analytical query combines selecting columns, grouping them, calculating aggregates, and sorting the final results. Let's return to the "Analytics with Adam" video to see all these pieces in action.

Want to Master SQL GROUP BY? Watch This Now

This video demonstrates how to use GROUP BY and ORDER BY together in a BigQuery environment.

First, watch the section explaining how to group by a column. Note how the query now returns multiple rows—one for each payment type. Next, skip to the end to see how to order the results. This shows how ORDER BY is used to sort the final aggregated output, for example, to find the payment type with the highest total revenue.

Filtering Before vs. After Grouping: WHERE and HAVING

You often need to filter data when performing aggregations. SQL provides two clauses for this, and they serve different purposes:

  • WHERE: Filters rows before the grouping and aggregation happen. Use it to exclude individual records from the calculation. For example: WHERE trip_distance > 0.
  • HAVING: Filters groups after the aggregation is complete. Use it to exclude summary rows that don't meet a condition. For example: HAVING COUNT(*) > 1000.

This distinction is a common source of confusion for beginners, but it's critical for writing correct queries. The next clip from the same video explains it perfectly.

Want to Master SQL GROUP BY? Watch This Now

Watch this segment to understand the difference between the WHERE and HAVING clauses.

Focus on the explanation from the presenter. He clearly explains the order of execution: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY. Understanding this order clarifies why WHERE filters raw data and HAVING filters aggregated results.

Practice Time

Now it's your turn to apply these concepts. The best way to learn SQL is by writing it.

SQL Lesson 10: Queries with aggregates (Pt. 1)

This interactive lesson from SQLBolt will let you practice writing queries with aggregate functions and GROUP BY.

First, read the brief explanation under the heading Grouped aggregate functions. Then, complete all four tasks in the exercise section at the bottom. Try to solve them yourself before looking at the solution.

Conclusion

In this lesson, you've made the leap from simply retrieving data to summarizing it. This is a fundamental skill in data engineering, used for everything from creating analytical reports to validating data quality.

Here are your key takeaways:

  • Aggregate functions like COUNT, SUM, AVG, MIN, and MAX compute a single summary value from a set of rows.
  • The GROUP BY clause is used to partition data into groups, allowing you to apply aggregate functions to each group separately.
  • Any non-aggregated column in the SELECT list must also appear in the GROUP BY clause.
  • WHERE filters rows before they are grouped, while HAVING filters entire groups after aggregation.

So far, all our queries have worked on a single table. In our next lesson, we will explore one of the most powerful features of relational databases: the ability to combine data from multiple related tables using INNER JOIN and LEFT JOIN.