DAY 8: SQL - GROUP BY & HAVING

📝 Setup Instructions:

1. Open SQLite Browser with your Superstore database
2. Today you'll learn SQL aggregation - the equivalent of Excel Pivot Tables!
3. GROUP BY groups rows together; HAVING filters grouped results

💡 GROUP BY = SQL's Pivot Table: Remember Day 7 (Excel Pivot Tables)? GROUP BY does the same thing in SQL - summarize data by categories! It's one of the most powerful SQL features.

Exercise 1: GROUP BY Basics - Total Sales by Region

Step-by-Step Instructions:

1. Type this query:

   SELECT
       Region,
       SUM(Sales) AS TotalSales
   FROM Orders
   GROUP BY Region
   ORDER BY TotalSales DESC;

2. Click Execute (▶️)
3. You'll see total sales for each region!

🔍 Understanding GROUP BY:

• GROUP BY Region = Group all rows with the same Region together
• SUM(Sales) = Calculate total sales for each group
• Result: One row per region with aggregated data
• This is like dragging Region to Rows and Sales to Values in a Pivot Table!

💡 Tips:

• Columns in SELECT must either be in GROUP BY or use aggregate functions
• Common aggregates: SUM, AVG, COUNT, MAX, MIN
• GROUP BY comes after WHERE, before ORDER BY

✅ Expected Result: 4 rows (one per region) with total sales for each

Exercise 2: Multiple Aggregates - Comprehensive Summary

Step-by-Step Instructions:

1. Calculate multiple metrics for each category:

   SELECT
       Category,
       COUNT(*) AS TotalOrders,
       SUM(Sales) AS TotalSales,
       AVG(Sales) AS AvgOrderValue,
       MAX(Sales) AS HighestSale,
       MIN(Sales) AS LowestSale
   FROM Orders
   GROUP BY Category
   ORDER BY TotalSales DESC;

2. This gives you a complete statistical summary per category!

💡 Multiple Aggregates:

• You can calculate many metrics in one query
• Each aggregate operates on the grouped rows
• Much more efficient than separate queries

✅ Expected Result: Each category with 6 different metrics

Exercise 3: GROUP BY Multiple Columns - Two Dimensions

Step-by-Step Instructions:

1. Group by both Region AND Category:

   SELECT
       Region,
       Category,
       COUNT(*) AS OrderCount,
       SUM(Sales) AS TotalSales
   FROM Orders
   GROUP BY Region, Category
   ORDER BY Region, TotalSales DESC;

2. You now see sales broken down by both dimensions!
3. This is like having both Region and Category in Rows in a Pivot Table

🎯 Multi-Column Grouping:

• Creates unique combinations of all grouped columns
• Order matters: GROUP BY Region, Category groups by Region first
• Can group by 3+ columns: GROUP BY Region, Category, Year

✅ Expected Result: Sales for each Region + Category combination (e.g., "East - Furniture", "East - Technology", etc.)

Exercise 4: Combining WHERE and GROUP BY - Filtered Aggregation

Step-by-Step Instructions:

1. Calculate sales by region, but only for profitable orders:

   SELECT
       Region,
       COUNT(*) AS ProfitableOrders,
       SUM(Sales) AS TotalSales,
       SUM(Profit) AS TotalProfit
   FROM Orders
   WHERE Profit > 0
   GROUP BY Region
   ORDER BY TotalProfit DESC;

2. WHERE filters BEFORE grouping (filters individual rows)

📌 WHERE vs HAVING:

• WHERE: Filters rows BEFORE grouping (filters raw data)
• HAVING: Filters groups AFTER aggregation (filters summarized data)
• Example: WHERE Profit > 0 (row level) vs HAVING SUM(Profit) > 1000 (group level)

✅ Expected Result: Regional sales including only profitable orders

Exercise 5: HAVING Clause - Filter Aggregated Results

Step-by-Step Instructions:

1. Find regions with total sales exceeding $50,000:

   SELECT
       Region,
       SUM(Sales) AS TotalSales,
       COUNT(*) AS OrderCount
   FROM Orders
   GROUP BY Region
   HAVING SUM(Sales) > 50000
   ORDER BY TotalSales DESC;

2. HAVING filters groups based on aggregate calculations
3. More examples:

   -- Customers with more than 10 orders
   SELECT CustomerID, COUNT(*) AS OrderCount
   FROM Orders
   GROUP BY CustomerID
   HAVING COUNT(*) > 10;
   
   -- Categories with average sale > $200
   SELECT Category, AVG(Sales) AS AvgSale
   FROM Orders
   GROUP BY Category
   HAVING AVG(Sales) > 200;

🎯 HAVING Use Cases:

• Find top customers (by order count or total spend)
• Identify high-performing products/categories
• Filter out insignificant groups (e.g., < 5 orders)
• Quality control (e.g., products with high defect rates)

💡 Query Order:

SELECT columns
FROM table
WHERE row_filters
GROUP BY columns
HAVING group_filters
ORDER BY columns
LIMIT n;

✅ Expected Result: Only regions meeting the $50,000 threshold

🎯 Practice Goal Checkpoint:

By completing these 5 exercises, you should now be able to:

• Use GROUP BY to aggregate data by categories
• Apply multiple aggregate functions (SUM, AVG, COUNT, MAX, MIN)
• Group by multiple columns for multi-dimensional analysis
• Combine WHERE with GROUP BY for filtered aggregation
• Use HAVING to filter grouped results
• Understand the difference between WHERE and HAVING

Key Insight: GROUP BY is SQL's answer to Excel Pivot Tables. While Excel uses drag-and-drop, SQL uses code - but both summarize data by categories!

Excel Day 7 vs SQL Day 8:

• Excel Pivot: Drag Region to Rows, Sales to Values
• SQL GROUP BY: SELECT Region, SUM(Sales) FROM Orders GROUP BY Region
• Same result, different interface!

Common GROUP BY Patterns:

• Sales analysis by time period, region, product
• Customer segmentation and ranking
• Inventory summaries
• Performance metrics and KPIs

Take a 5-minute break, then move on to Independent Practice!