**Course Title:** SQL Mastery: From Fundamentals to Advanced Techniques **Section Title:** SQL Functions and Operators **Topic:** Write queries using aggregate functions and grouping data for summary reports In this lab topic, we will delve into the world of aggregate functions and grouping data to generate summary reports. We will explore how to write queries that provide valuable insights into our data, and how to use these insights to make informed decisions. **What are Aggregate Functions?** Aggregate functions are a set of functions that allow us to perform calculations on a group of rows and return a single value. We have already covered some basic aggregate functions such as `COUNT`, `SUM`, `AVG`, `MIN`, and `MAX` in previous topics. These functions are essential in generating summary reports and providing insights into our data. **What is Grouping Data?** Grouping data is the process of dividing our data into groups based on one or more columns. This allows us to perform aggregate functions on each group separately, providing us with valuable insights into our data. We use the `GROUP BY` clause to group our data. **Writing Queries using Aggregate Functions and Grouping Data** Let's consider an example to illustrate how we can write queries using aggregate functions and grouping data. Suppose we have a `sales` table that contains information about our sales transactions: | `sale_id` | `product_id` | `quantity` | `price` | `date` | | --- | --- | --- | --- | --- | | 1 | 1 | 10 | 100 | 2022-01-01 | | 2 | 1 | 20 | 120 | 2022-01-02 | | 3 | 2 | 15 | 80 | 2022-01-03 | | 4 | 3 | 30 | 150 | 2022-01-04 | | 5 | 1 | 25 | 110 | 2022-01-05 | We want to generate a summary report that shows the total quantity sold and the total revenue generated for each product. ```sql SELECT product_id, SUM(quantity) AS total_quantity, SUM(price * quantity) AS total_revenue FROM sales GROUP BY product_id; ``` This query will return a result set with the product ID, total quantity sold, and total revenue generated for each product. | `product_id` | `total_quantity` | `total_revenue` | | --- | --- | --- | | 1 | 55 | 6300 | | 2 | 15 | 1200 | | 3 | 30 | 4500 | **Using HAVING Clause** The `HAVING` clause is used to filter the groups based on a condition. For example, we can use the `HAVING` clause to filter the groups that have a total revenue greater than $5000. ```sql SELECT product_id, SUM(quantity) AS total_quantity, SUM(price * quantity) AS total_revenue FROM sales GROUP BY product_id HAVING SUM(price * quantity) > 5000; ``` This query will return a result set with the product ID, total quantity sold, and total revenue generated for each product that has a total revenue greater than $5000. **Practical Takeaways** * Use aggregate functions to perform calculations on a group of rows and return a single value. * Use the `GROUP BY` clause to group our data based on one or more columns. * Use the `HAVING` clause to filter the groups based on a condition. * Always test your queries on a sample data set to ensure that they are working correctly. **Lab Exercise** Try the following lab exercise to reinforce your understanding of writing queries using aggregate functions and grouping data: * Create a `products` table that contains information about our products. * Create a `sales` table that contains information about our sales transactions. * Write a query that generates a summary report that shows the total quantity sold and the total revenue generated for each product. * Use the `HAVING` clause to filter the groups that have a total revenue greater than $1000. **Feedback** If you have any questions or need help with the lab exercise, please leave a comment below. We will respond to your comment within 24 hours. **References** * [MySQL Aggregate Functions](https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html) * [PostgreSQL Aggregate Functions](https://www.postgresql.org/docs/current/functions-aggregate.html) In the next topic, we will cover understanding relationships between tables: Primary and Foreign Keys. Please proceed to the next topic to learn more.

SELECT 
    product_id,
    SUM(quantity) AS total_quantity,
    SUM(price * quantity) AS total_revenue
FROM 
    sales
GROUP BY 
    product_id;

SELECT 
    product_id,
    SUM(quantity) AS total_quantity,
    SUM(price * quantity) AS total_revenue
FROM 
    sales
GROUP BY 
    product_id
HAVING 
    SUM(price * quantity) > 5000;