From the course: Oracle Database 12c: Basic SQL

Introduction to aggregate functions - Oracle Database Tutorial

From the course: Oracle Database 12c: Basic SQL

Start my 1-month free trial

Introduction to aggregate functions

- Aggregate functions in SQL allows you to perform calculation on a set of values and return a single value as the output. These aggregate functions are often used alongside a Group By clause, which allows you to partition your tables into individual categories based on one or more columns. So, for example, say that your Employees table has a Dept ID column and a Salary column. For each employee, showing the department ID to which this employee is assigned and the salary for that employee, and say that in your query you want to see the highest earning employee in each department but only the highest earning employee. So in order to accomplish that, your SQL query will first have to segment the Employees table into individual partitions or groups based on the Department ID column and then for each set of rows that belong to a specific department ID, produce one row showing only the highest earning employee. So we can do that using the Max Aggregate Function on the Salary column, grouping by Department ID. We'll see examples later in our chapter. There are many aggregate functions that exist as part of the SQL language in Oracle. Some of them are Avg or average, showing the average value for a set of rows, Count, which basically counts the number of records in a given set of rows or a group, Min and Max which shows you the minimum value and maximum value, and Sum, which summarizes the values in a column for a specific set of rows. We'll see these functions in action later in our chapter.

Contents