Segmenting Data with GROUP BY

The Power of Segments

In the previous lesson, we found single numbers like total sales. But often, we need to break these down. Instead of just 'Total Sales,' we need 'Total Sales per category.' This is where the GROUP BY clause becomes your most powerful tool.

Imagine you have a giant pile of laundry. If you use COUNT, you get the total number of items. But if you want to know how many shirts, pants, and socks you have, you first have to sort them into baskets. GROUP BY tells SQL how to sort your data into 'baskets' before it performs the math.

• Aggregate functions calculate single values.
• GROUP BY segments these calculations into categories.
• It acts like a sorting mechanism for your data.

Our E-commerce Schema

To master GROUP BY, we will use our standard E-commerce tables. Notice the Category in Products and CustomerID in Orders—these are the 'dimensions' we usually group by.

Let's look at our schema. In the Products table, the Category column is perfect for grouping. In the Orders table, we can group by CustomerID to see individual buying habits. Keep these columns in mind as we build our queries.

• Products Table: Category is the grouping dimension.
• Orders Table: CustomerID is the grouping dimension.

How GROUP BY Works

The GROUP BY clause always comes after the FROM and WHERE clauses, but before ORDER BY.

SELECT Category, COUNT(ProductID)
FROM Products
GROUP BY Category;

Watch how SQL processes this query. First, it looks at the Category column. It identifies every unique category, like 'Electronics' and 'Clothing'. It puts all 'Electronics' rows in one group and 'Clothing' in another. Finally, it runs the COUNT function on each group separately to give you the final result.

• 1. Identify unique categories.
• 2. Group rows together.
• 3. Run the aggregate function on each group.

The Golden Rule

The most common error for beginners is the Non-Aggregated Column mistake. If a column is in your SELECT list and it is NOT inside a function, it MUST be in the GROUP BY.

Look at this incorrect query. The 'Name' column is in the SELECT but not in the GROUP BY. SQL gets confused! It doesn't know which 'Name' to show for the whole category. To fix it, you must either remove 'Name' or add it to the GROUP BY clause.

• Every SELECT column must be an aggregate OR in the GROUP BY clause.
• SQL cannot pick a single value for a non-grouped column.

Exercise 1: Beginner Challenge

Write a query to find the total number of products in each Category from the Products table.

It's time for your first challenge. Write a query to count the products in each category. Remember the Golden Rule!

• Use SELECT Category
• Use COUNT(*)
• Use GROUP BY Category

Exercise 2: Intermediate Challenge

Calculate the total revenue (SUM of TotalAmount) generated by each CustomerID from the Orders table.

Great start. Now, let's look at the Orders table. Can you find the total amount spent by each customer?

• Target the Orders table.
• Group by CustomerID.
• Aggregate the TotalAmount column.

Exercise 3: Advanced Challenge

Find the average order value (AVG) for each customer, but ONLY for orders placed after '2023-01-01'.

Final challenge! You need to filter the data by date first, then group it. Remember the order: FROM, then WHERE, then GROUP BY.

• Include a WHERE clause for the date.
• The WHERE clause must come BEFORE the GROUP BY.
• Use the AVG function.