**Course Title:** SQLite Mastery: Lightweight Database Management **Section Title:** Aggregate Functions and Grouping Data **Topic:** Write queries to aggregate and group data for reporting purposes.(Lab topic) **Introduction:** In this lab topic, you'll learn how to write advanced queries to aggregate and group data for reporting purposes. You'll explore real-world scenarios, hands-on exercises, and examples that will help you master the concepts of aggregate functions and grouping data in SQLite. By the end of this topic, you'll be able to write efficient queries that produce insights and meaningful results. **Understanding Aggregate Functions and Grouping:** Before diving into reporting, let's review the key concepts of aggregate functions and grouping. * Aggregate functions (e.g., `COUNT`, `SUM`, `AVG`, `MIN`, `MAX`) combine multiple values into a single value. * Grouping (using `GROUP BY`) divides a result set into subsets based on one or more columns. **Lab Exercise 1: Simple Reporting** Imagine you're a sales manager for an e-commerce company, and you want to generate a report showing the total sales for each region. ```sql -- Create a sample table CREATE TABLE sales ( id INTEGER PRIMARY KEY, region TEXT, sales_amount REAL ); -- Insert sample data INSERT INTO sales (region, sales_amount) VALUES ('North', 1000), ('South', 800), ('East', 1200), ('West', 900), ('North', 1100), ('South', 700), ('East', 1300), ('West', 1000); -- Write a query to generate the report SELECT region, SUM(sales_amount) AS total_sales FROM sales GROUP BY region; ``` **Expected Output:** ```markdown region | total_sales -------|----------- North | 2100 South | 1500 East | 2500 West | 1900 ``` **Lab Exercise 2: Filtering and Sorting** Suppose you want to generate a report showing the top 3 sales regions with the highest total sales. ```sql -- Modify the previous query to filter and sort the results SELECT region, SUM(sales_amount) AS total_sales FROM sales GROUP BY region ORDER BY total_sales DESC LIMIT 3; ``` **Expected Output:** ```markdown region | total_sales -------|----------- East | 2500 North | 2100 West | 1900 ``` **Lab Exercise 3: Advanced Reporting** Imagine you want to generate a report showing the average sales per region, with a filter for regions having an average sales greater than $500. ```sql -- Write a query to generate the report SELECT region, AVG(sales_amount) AS avg_sales FROM sales GROUP BY region HAVING avg_sales > 500; ``` **Expected Output:** ```markdown region | avg_sales -------|----------- East | 1250.0 North | 1050.0 West | 950.0 ``` **Conclusion:** In this lab topic, you've learned how to write advanced queries to aggregate and group data for reporting purposes. You've explored real-world scenarios and practiced hands-on exercises to master the concepts. Takeaway: * Use aggregate functions to combine multiple values into a single value. * Use `GROUP BY` to divide a result set into subsets based on one or more columns. * Use `HAVING` to filter grouped results based on conditions. **Additional Resources:** * SQLite documentation: [Aggregate Functions](https://www.sqlite.org/lang_aggfunc.html) * SQLite documentation: [GROUP BY](https://www.sqlite.org/lang_select.html#grouping) **Leave a Comment/Ask for Help:** If you have any questions or need help with the lab exercises, feel free to leave a comment below. We'll be happy to assist you. **What's Next:** In the next topic, we'll explore the world of table relationships and foreign keys. You'll learn how to create and manage relationships between tables, and how to write queries that leverage these relationships.