AGGREGATE-PROCESSING-REPORT.pptx

OCTOBER 2024 AGGREGATE PROCESSING AGGREGATE FUNCTION REPORTERS: Gerry Yan Landeza Marnie C. Lavin Hannah M. Esporlas  SQL Aggregate Functions Aggregate functions in SQL perform some calculations on more than one value to return a single value....

OCTOBER 2024 AGGREGATE PROCESSING AGGREGATE FUNCTION REPORTERS: Gerry Yan Landeza Marnie C. Lavin Hannah M. Esporlas  SQL Aggregate Functions Aggregate functions in SQL perform some calculations on more than one value to return a single value. There are many aggregate functions in SQL, including average, count, sum, min, and max. All aggregate functions ignore NULL values while calculating, except the Count function. An SQL aggregate function calculates on a set of values and returns a single value. For example, the average function ( AVG) takes a list of values and returns the average. Because an aggregate function operates on a set of values, it is often used with the GROUP BY clause of the SELECT statement. The GROUP BY clause divides the result set into groups of values and the aggregate function returns a single value for each group. https://www.simplilearn.com/tutorials/sql-tutorial/sql-aggregate-functions  Five types of SQL aggregate functions The following are the commonly used SQL aggregate functions: AVG() – returns the average of a set. COUNT() – returns the number of items in a set. MAX() – returns the maximum value in a set. MIN() – returns the minimum value in a set SUM() – returns the sum of all or distinct values in a set Except for the COUNT() function, SQL aggregate functions ignore null. You can use aggregate functions as expressions only in the following: The select list of a SELECT statement, either a subquery or an outer query. A HAVING clause  Column References In SQL, aggregate functions are crucial in calculating a set of values and returning a single value. These functions are particularly useful when dealing with large datasets. When using aggregate functions in SQL, it is important to understand column references. A column reference is a name containing the data you want to aggregate. To use an aggregate function with a column reference, specify the column's name in the function's parentheses. For example, to find the average salary of employees in a table called "employees", you would use the AVG function with the column reference "salary" like this: SELECT AVG (salary) FROM employees; Using column aliases instead of column references is also possible for a more readable code. However, understanding column references is essential when working with SQL aggregate functions. https://www.simplilearn.com/tutorials/sql-tutorial/sql-aggregate-functions  Five types of SQL Aggregate Functions 1. COUNT() Function The COUNT() function returns the number of items in a set. The following shows the syntax of the COUNT() function: COUNT ( [ALL | DISTINCT] column | expression | *) For example, the following example uses the COUNT(*) function to return the headcount of each department: SELECT department_name, COUNT(*) headcount FROM employees INNER JOIN departments USING (department_id) GROUP BY department_name ORDER BY department_name; https://www.simplilearn.com/tutorials/sql-tutorial/sql-aggregate-functions  Five types of SQL Aggregate Functions 2. SUM() Function The SUM() function returns the sum of all values. The following illustrates the syntax of the SUM() function: SUM(ALL | DISTINCT column) For example, the following statement returns the total salary of all employees in each department: SELECT department_id, SUM(salary) FROM employees GROUP BY department_id; https://www.simplilearn.com/tutorials/sql-tutorial/sql-aggregate-functions  Five types of SQL Aggregate Functions 3. AVG() Function The AVG() function returns the average values in a set. The following illustrates the syntax of the AVG() function: AVG( ALL | DISTINCT) The ALL keyword instructs the AVG() function to calculate the average of all values while the DISTINCT keyword forces the function to operate on distinct values only. By default, the ALL option is used. The following example shows how to use the AVG() function to calculate the average salary of each department: SELECT department_name, ROUND(AVG(salary), 0) avg_salary FROM employees INNER JOIN departments USING (department_id) GROUP BY department_name ORDER BY department_name; https://www.simplilearn.com/tutorials/sql-tutorial/sql-aggregate-functions  Five types of SQL Aggregate Functions 4. MIN() Function The MIN() function returns the minimum value of a set. The following illustrates the syntax of the MIN() function: MIN(column | expression) For example, the following statement returns the minimum salary of the employees in each department: SELECT department_name, MIN(salary) min_salary FROM employees INNER JOIN departments USING (department_id) GROUP BY department_name ORDER BY department_name; https://www.simplilearn.com/tutorials/sql-tutorial/sql-aggregate-functions  Five types of SQL Aggregate Functions 5. MAX() Function The MAX() function returns the maximum value of a set. The MAX() function has the following syntax: MAX(column | expression) For example, the following statement returns the highest salary of employees in each department: SELECT department_name, MAX(salary) highest_salary FROM employees INNER JOIN departments USING (department_id) GROUP BY department_name ORDER BY department_name; https://www.simplilearn.com/tutorials/sql-tutorial/sql-aggregate-functions  Why Use Aggregate Functions? It is also important to understand why to use aggregate functions after understanding what an aggregate function is in SQL. Aggregate functions are the most essential ingredient of any database management system. They help us perform many calculations over massive data sets faster and more efficiently. For example, these functions create statements of statistics, drive financial analysis, and maintain inventory levels. Furthermore, aggregate functions can be applied to further our understanding of the data at hand. Knowing the average price of all products stored in our warehouse or the total sales amount over a period is pretty easy. However, aggregate functions would have to be replaced by checking every data point on our own, which is time-consuming and full of potential failures. Aggregate functions in SQL are important in general to anyone working in large amounts of data, trying to seek valuable insight. https://www.simplilearn.com/tutorials/sql-tutorial/sql-aggregate-functions REFERENCES: https://www.simplilearn.com/tutorials/sql-tutorial/sql-aggregate-functions https://learnsql.com/blog/aggregate-function-in-sql/ THANKYOU! @reallygreatsite