**Course Title:** SQL Mastery: From Fundamentals to Advanced Techniques **Section Title:** SQL Functions and Operators **Topic:** Using aggregate functions: COUNT, SUM, AVG, MIN, MAX. **Overview** In this topic, we will explore the world of aggregate functions in SQL, which allow us to perform calculations on a set of data and return a single value. We will cover five essential aggregate functions: COUNT, SUM, AVG, MIN, and MAX. By the end of this topic, you will be able to use these functions to analyze and summarize data effectively. **What are Aggregate Functions?** Aggregate functions are a type of SQL function that perform calculations on a set of data and return a single value. They are used to summarize data, calculate statistics, and perform other types of data analysis. Aggregate functions are often used in combination with GROUP BY and HAVING clauses to group and filter data. **Aggregate Functions: COUNT, SUM, AVG, MIN, MAX** Let's dive into the five aggregate functions we will be covering in this topic. ### 1. COUNT The COUNT function returns the number of rows in a table that match a specified condition. It can be used to count all rows in a table or to count rows that meet specific conditions. **Example:** ```sql SELECT COUNT(*) FROM customers; ``` This query returns the total number of customers in the `customers` table. **Example with condition:** ```sql SELECT COUNT(*) FROM customers WHERE country = 'USA'; ``` This query returns the number of customers from the USA. ### 2. SUM The SUM function returns the sum of all values in a specified column. **Example:** ```sql SELECT SUM(salary) FROM employees; ``` This query returns the total salary of all employees in the `employees` table. ### 3. AVG The AVG function returns the average value of all values in a specified column. **Example:** ```sql SELECT AVG(salary) FROM employees; ``` This query returns the average salary of all employees in the `employees` table. ### 4. MIN The MIN function returns the minimum value of all values in a specified column. **Example:** ```sql SELECT MIN(salary) FROM employees; ``` This query returns the lowest salary of all employees in the `employees` table. ### 5. MAX The MAX function returns the maximum value of all values in a specified column. **Example:** ```sql SELECT MAX(salary) FROM employees; ``` This query returns the highest salary of all employees in the `employees` table. **Combining Aggregate Functions** You can combine aggregate functions to perform more complex calculations. **Example:** ```sql SELECT COUNT(*), SUM(salary), AVG(salary), MIN(salary), MAX(salary) FROM employees; ``` This query returns a count of employees, the total salary, the average salary, the lowest salary, and the highest salary. **best Practices** * Always use a column name with aggregate functions, unless you are using the COUNT(\*) function. * Use the GROUP BY clause to group data before applying aggregate functions. * Use the HAVING clause to filter data after applying aggregate functions. **Common Mistakes** * Using the SUM function on a non-numeric column. * Using the AVG function on a non-numeric column. * Forgetting to include a column name with an aggregate function. **Conclusion** In this topic, we covered the five essential aggregate functions in SQL: COUNT, SUM, AVG, MIN, and MAX. We learned how to use these functions to analyze and summarize data. Remember to always use a column name with aggregate functions and to combine functions to perform more complex calculations. **Additional Resources** * For more information on aggregate functions, see the official MySQL documentation: [Aggregate Functions](https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html) * For practice exercises, check out this SQL Fiddle: [SQL Fiddle](http://sqlfiddle.com) **What's Next?** In the next topic, we will cover **Performing calculations with arithmetic operators.** Do you have any questions or need help with this topic? You can ask in the comments section below.