**Course Title:** SQLite Mastery: Lightweight Database Management **Section Title:** Aggregate Functions and Grouping Data **Topic:** Advanced data aggregation techniques ### Overview In the previous topics, we covered the fundamentals of aggregate functions and grouping data in SQLite. Now, we'll explore advanced data aggregation techniques to help you extract insights from your data. By the end of this topic, you'll be able to analyze complex data with ease. ### ROLLUP and CUBE SQLite supports two advanced grouping functions: ROLLUP and CUBE. These functions allow you to generate multiple levels of subtotals and totals in a single query. **ROLLUP** ROLLUP is used to generate subtotals for each level of a grouping hierarchy. The syntax is: ```sql SELECT ... GROUP BY ROLLUP (column1, column2, ...) ``` For example, let's say we have a table `sales` with columns `region`, `country`, and `sales_amount`. We can use ROLLUP to generate subtotals for each country and region: ```sql CREATE TABLE sales ( region TEXT, country TEXT, sales_amount REAL ); INSERT INTO sales VALUES ('North', 'USA', 100), ('North', 'Canada', 200), ('South', 'Brazil', 300), ('South', 'Argentina', 400); SELECT region, country, SUM(sales_amount) AS total_sales FROM sales GROUP BY ROLLUP (region, country); ``` This will output: ``` +--------+---------+------------+ | region | country | total_sales | +--------+---------+------------+ | North | USA | 100 | | North | Canada | 200 | | North | NULL | 300 | | South | Brazil | 300 | | South | Argentina| 400 | | South | NULL | 700 | | NULL | NULL | 1000 | +--------+---------+------------+ ``` **CUBE** CUBE generates all possible subtotals for a given set of columns. The syntax is: ```sql SELECT ... GROUP BY CUBE (column1, column2, ...) ``` For example, let's use CUBE to generate all subtotals for the `sales` table: ```sql SELECT region, country, SUM(sales_amount) AS total_sales FROM sales GROUP BY CUBE (region, country); ``` This will output: ``` +--------+---------+------------+ | region | country | total_sales | +--------+---------+------------+ | North | USA | 100 | | North | Canada | 200 | | North | Brazil | 0 | | North | Argentina| 0 | | South | USA | 0 | | South | Canada | 0 | | South | Brazil | 300 | | South | Argentina| 400 | | NULL | USA | 100 | | NULL | Canada | 200 | | NULL | Brazil | 300 | | NULL | Argentina| 400 | | North | NULL | 300 | | South | NULL | 700 | | NULL | NULL | 1000 | +--------+---------+------------+ ``` As you can see, CUBE generates all possible subtotals, including those for missing combinations. ### Grouping Sets Grouping sets allow you to specify multiple grouping hierarchies in a single query. The syntax is: ```sql SELECT ... GROUP BY GROUPING SETS (grouping_set1, grouping_set2, ...) ``` For example, let's say we want to group the `sales` table by `region` and `country`, as well as by `region` only: ```sql SELECT region, country, SUM(sales_amount) AS total_sales FROM sales GROUP BY GROUPING SETS ( (region, country), (region) ); ``` This will output: ``` +--------+---------+------------+ | region | country | total_sales | +--------+---------+------------+ | North | USA | 100 | | North | Canada | 200 | | South | Brazil | 300 | | South | Argentina| 400 | | North | NULL | 300 | | South | NULL | 700 | +--------+---------+------------+ ``` ### Common Table Expressions (CTEs) CTEs are temporary result sets that you can reference within a query. They're useful for simplifying complex queries. For example, let's say we want to calculate the total sales for each region, and then use that result to calculate the total sales for each country: ```sql WITH region_sales AS ( SELECT region, SUM(sales_amount) AS total_region_sales FROM sales GROUP BY region ) SELECT country, SUM(sales_amount) AS total_country_sales FROM sales JOIN region_sales ON sales.region = region_sales.region GROUP BY country; ``` This will output: ``` +---------+-------------------+ | country | total_country_sales | +---------+-------------------+ | USA | 100 | | Canada | 200 | | Brazil | 300 | | Argentina| 400 | +---------+-------------------+ ``` ### Window Functions Window functions allow you to calculate values across rows that are related to the current row, such as cumulative sums or moving averages. For example, let's say we want to calculate the running total of sales for each country: ```sql SELECT country, sales_amount, SUM(sales_amount) OVER (PARTITION BY country ORDER BY sales_amount) AS running_total FROM sales; ``` This will output: ``` +---------+------------+--------------+ | country | sales_amount | running_total | +---------+------------+--------------+ | USA | 100 | 100 | | USA | 200 | 300 | | Canada | 300 | 300 | | Canada | 400 | 700 | | Brazil | 100 | 100 | | Brazil | 200 | 300 | | Argentina| 300 | 300 | | Argentina| 400 | 700 | +---------+------------+--------------+ ``` For more information on window functions, check out the SQLite documentation: <https://sqlite.org/lang_window.html> ### Exercises 1. Use ROLLUP to generate subtotals for the `sales` table by `region` and `country`. 2. Use CUBE to generate all possible subtotals for the `sales` table. 3. Use grouping sets to group the `sales` table by `region` and `country`, as well as by `region` only. ### Conclusion In this topic, we covered advanced data aggregation techniques in SQLite, including ROLLUP, CUBE, grouping sets, common table expressions, and window functions. These techniques will help you analyze complex data and extract valuable insights. **Leave a comment below with any questions or feedback!** **What's next?** We'll explore table relationships and foreign keys in the next topic: "Understanding table relationships and foreign keys".