**Course Title:** SQLite Mastery: Lightweight Database Management **Section Title:** Aggregate Functions and Grouping Data **Topic:** Using aggregate functions in SQLite: COUNT, SUM, AVG, MIN, MAX. **Overview** ===================================== In this topic, we'll explore the use of aggregate functions in SQLite to perform calculations on sets of data. Aggregate functions allow you to summarize and analyze large datasets, generating meaningful insights from the data. We'll cover five essential aggregate functions: COUNT, SUM, AVG, MIN, and MAX. By the end of this topic, you'll understand how to use these functions to extract valuable information from your SQLite databases. **COUNT (Aggregate Function)** ----------------------------- The COUNT() function returns the number of rows in a table or the number of non-NULL values in a specific column. The syntax for COUNT() is as follows: ```sql COUNT(column_name) COUNT(*) ``` * `COUNT(column_name)`: Returns the number of non-NULL values in the specified column. * `COUNT(*)`: Returns the total number of rows in the table. **Example 1: COUNT()** Suppose we have a table called "orders" with the following structure and data: | OrderID | CustomerName | OrderDate | | --- | --- | --- | | 1 | John Smith | 2022-01-01 | | 2 | Jane Doe | 2022-01-02 | | 3 | John Smith | 2022-01-03 | | 4 | Jane Doe | 2022-01-04 | | 5 | John Smith | 2022-01-05 | To count the total number of rows in the "orders" table, use the following query: ```sql SELECT COUNT(*) as TotalOrders FROM orders; ``` Result: | TotalOrders | | --- | | 5 | To count the number of non-NULL values in the "CustomerName" column, use the following query: ```sql SELECT COUNT(CustomerName) as TotalCustomers FROM orders; ``` Result: | TotalCustomers | | --- | | 5 | **SUM (Aggregate Function)** --------------------------- The SUM() function calculates the total sum of a set of values. The syntax for SUM() is as follows: ```sql SUM(column_name) ``` **Example 2: SUM()** Continuing with the "orders" table, suppose we have an additional column "OrderTotal" representing the total value of each order: | OrderID | CustomerName | OrderDate | OrderTotal | | --- | --- | --- | --- | | 1 | John Smith | 2022-01-01 | 100.00 | | 2 | Jane Doe | 2022-01-02 | 200.00 | | 3 | John Smith | 2022-01-03 | 50.00 | | 4 | Jane Doe | 2022-01-04 | 150.00 | | 5 | John Smith | 2022-01-05 | 250.00 | To calculate the total sum of the "OrderTotal" column, use the following query: ```sql SELECT SUM(OrderTotal) as TotalOrderValue FROM orders; ``` Result: | TotalOrderValue | | --- | | 750.00 | **AVG (Aggregate Function)** ------------------------- The AVG() function calculates the average value of a set of values. The syntax for AVG() is as follows: ```sql AVG(column_name) ``` **Example 3: AVG()** Using the "orders" table with the "OrderTotal" column, to calculate the average order value, use the following query: ```sql SELECT AVG(OrderTotal) as AverageOrderValue FROM orders; ``` Result: | AverageOrderValue | | --- | | 150.00 | **MIN and MAX (Aggregate Functions)** ---------------------------------- The MIN() and MAX() functions return the minimum and maximum values in a set of values, respectively. **MIN()** ```sql MIN(column_name) ``` **MAX()** ```sql MAX(column_name) ``` **Example 4: MIN() and MAX()** Using the "orders" table with the "OrderTotal" column, to find the minimum and maximum order values, use the following queries: ```sql SELECT MIN(OrderTotal) as MinimumOrderValue FROM orders; SELECT MAX(OrderTotal) as MaximumOrderValue FROM orders; ``` Results: | MinimumOrderValue | | --- | | 50.00 | | MaximumOrderValue | | --- | | 250.00 | **Best Practices and Common Use Cases** -------------------------------------- When using aggregate functions, keep the following best practices in mind: * Always specify the column name(s) you want to aggregate. * Use aliasing to make your results more readable. * Be mindful of NULL values and how they affect your results. Common use cases for aggregate functions include: * Calculating total sales or revenue * Determining average prices or ratings * Finding minimum and maximum values in a dataset **Conclusion** ============== In this topic, we covered five essential aggregate functions in SQLite: COUNT, SUM, AVG, MIN, and MAX. We explored the syntax and usage of each function with examples, highlighting common use cases and best practices. By mastering these aggregate functions, you'll be able to extract valuable insights from your SQLite databases. **What's Next?** ================ In the next topic, we'll explore "Grouping data with GROUP BY." We'll cover how to group rows of data according to one or more columns, applying aggregate functions to each group. Stay tuned! **Need Help or Have Questions?** ============================= If you're having trouble understanding any of the concepts covered in this topic, feel free to ask for help or leave a comment below.