Summarizing Data with GROUP BY and Aggregate Functions

1. Understanding Aggregate Functions

Aggregate functions take a set of rows as input and return a single summary value. The five most common aggregate functions, which you need to know for the certification exam, are:

• COUNT(): Counts the number of rows.
• SUM(): Calculates the sum of a set of values.
• AVG(): Calculates the average of a set of values.
• MIN(): Finds the minimum value in a set.
• MAX(): Finds the maximum value in a set.

Let's start with a quick video that introduces these five functions and shows what they do.

A Closer Look at COUNT

The COUNT function is particularly versatile and has a few variations you must understand. The key difference lies in how they handle NULL values and whether they count all rows or only unique values.

• COUNT(*): Counts all rows in the group, including duplicates and rows with NULL values. This is the most common way to get a total row count.
• COUNT(column_name): Counts all rows where column_name is not NULL. It ignores NULLs.
• COUNT(DISTINCT column_name): Counts the number of unique, non-NULL values in column_name.

Understanding this distinction is crucial for accurate data analysis. For example, COUNT(*) from a customers table gives you the total number of customer records, while COUNT(phone_number) tells you how many of those customers have a phone number on file.

This next video provides an excellent, detailed explanation of these nuances.

2. Grouping Aggregations with GROUP BY

So far, we've applied aggregate functions to an entire table. But what if you want to find the total sales for each product category? Or the number of employees in each department?

This is where the GROUP BY clause comes in. It divides the rows of a table into groups based on the values in one or more columns. The aggregate functions then calculate a result for each of these groups.

A critical rule to remember is: Any column in your SELECT list that is not part of an aggregate function must be included in the GROUP BY clause. If you violate this rule, the database won't know which row's value to display for a given group, and it will raise an error.

Let's watch a detailed walkthrough of how GROUP BY works.

Grouping by Multiple Columns and Using Joins

You can make your aggregations even more granular by grouping by multiple columns. For example, you could group sales data BY region, product_category to see a performance breakdown for each category within each region.

You can also combine GROUP BY with the JOINs you learned in the previous lesson. This is a very common pattern in data engineering. You might join an orders table with a customers table to find the total number of orders per customer.

The following video segment shows a practical example of this.

3. Hands-On Practice in Databricks

Now it's time to apply these concepts in your Databricks workspace.

Instructions:

1. Navigate to the SQL Editor in your Databricks workspace.
2. Make sure a SQL Warehouse is running and selected.
3. Copy and paste the following code into the editor and run it. This creates two temporary views (dealer and dealership_info) that we will use for our exercises.

CREATE OR REPLACE TEMP VIEW dealer(id, city, car_model, quantity) AS
VALUES
  (100, 'Fremont', 'Honda Civic', 10),
  (100, 'Fremont', 'Honda Accord', 15),
  (100, 'Fremont', 'Honda CRV', 7),
  (200, 'Dublin', 'Honda Civic', 20),
  (200, 'Dublin', 'Honda Accord', 10),
  (200, 'Dublin', 'Honda CRV', 3),
  (300, 'San Jose', 'Honda Civic', 5),
  (300, 'San Jose', 'Honda Accord', 8);

CREATE OR REPLACE TEMP VIEW dealership_info(dealership_id, name) AS
VALUES
  (100, 'Fremont Auto'),
  (200, 'Dublin Cars'),
  (300, 'SJ Motors');

-- You can query them to see their contents
-- SELECT * FROM dealer;
-- SELECT * FROM dealership_info;

Now, write a SQL query for each of the following tasks. Try to solve them yourself before looking at any examples.

Practice Questions:

1. Simple Aggregation: Calculate the total number of cars (quantity) available across all dealerships.
2. Single-Column GROUP BY: Find the total quantity of cars for each dealership id.
3. Multiple Aggregations: For each dealership id, find the total inventory (SUM), the number of different car models they carry (COUNT), and the largest number of any single car model they have in stock (MAX).
4. GROUP BY with JOIN: Write a query that shows the dealership name (not the ID) and its total car inventory. You will need to join dealer and dealership_info.
5. Multi-Column GROUP BY: Count how many dealerships in each city carry each car_model.

You can check your work against the official Databricks documentation, which contains similar examples.

Conclusion

Excellent work! You have now mastered aggregation, one of the most fundamental and powerful capabilities of SQL. Combining SELECT, JOIN, and GROUP BY allows you to transform raw, row-level data into meaningful, high-level summaries.

Key Takeaways

• Aggregate functions (COUNT, SUM, AVG, MIN, MAX) perform a calculation on a set of rows and return a single value.
• The GROUP BY clause is used to partition rows into groups, so aggregate functions can be applied to each group individually.
• The distinction between COUNT(*), COUNT(column), and COUNT(DISTINCT column) is important for handling NULLs and duplicates correctly.
• Any non-aggregated column in the SELECT list must also be present in the GROUP BY clause.
• You can (and often will) combine JOINs with GROUP BY to create aggregated reports from multiple tables.

Next Steps

In the upcoming lesson, we will explore CASE expressions. This will add another layer of power to your queries by allowing you to implement conditional logic, similar to if/then/else statements in other languages. You'll be able to create your own custom categories on the fly and then use them within your GROUP BY clauses for even more sophisticated analysis.