How to use Oracle Aggregate Functions?

Aggregate functions are multi-row oracle functions, that operate on a set of rows and return one result or one result per group.

Aggregate functions can help you to answer some basic and complex questions.

Examine the below table named EMPLOYEES.



And below are some questions that could be answered by using appropriate Oracle aggregate functions using SQL query.

How many employees are working in the company?

What is the average salary for employees provided by the company?

What is the maximum or minimum salary provided by the company, and to whom?

Read more about

How to use SQL having clause to filter the grouped rows?

Types of Aggregate functions

• COUNT 


• AVG 


• SUM 


• MAX 


• MIN 

Each function takes an argument and returns the single output for each row

SQL Count() aggregate function

There are two types of Oracle COUNT () aggregate function

• count(c) :c is a column of which you want to count the record


• count(*): It counts all the records of a table including the null values.

NOTE: All the group functions ignore null values except COUNT(*) aggregate function.

Examples

Consider table named employees for practicals



Now we will use the count aggregate function to count the total number of records in column salary of above table, let's see how many records it show

It is showing only 4 records because it ignores the null value

Using SQL COUNT(*) aggregate function

This function ignores the null values and counts the total number of records from a table.

Using SQL AVG() function

It finds the average of a given column's value, it works only with the numeric data.

Examples


Using SQL SUM() aggregate function

This function is used to find the total sum of the column values, It also works only with numeric data.

Example

NOTE: TOTAL_SALARY_PROVIDED is a column alias which we have provided for complex expression.

Using SQL MAX/MIN aggregate function

As the name suggests, these aggregate functions are used to retrieves the highest and lowest value from a specified column.

Example



Using GROUP BY clause

The Group by clause is used with the Oracle aggregate functions (that we have learned above) to group the result set by one or multiple columns

Syntax

SQL>SELECT col_1,col_2,col_3,GROUP_FUNCTION(col_4)
FROM table_name 
WHERE condition 
GROUP BY col_1,col_2,col_3;

Note: All the columns or expressions specified in the SELECT list must be placed in GROUP BY clause except grouped columns

GROUP BY clause can be used with all of the aggregate function

Examples

Consider table EMPLOYEES2 for practice

Using GROUP BY clause with MAX AGGREGATE function



Now to understand more clearly, just focus on above query and given description

As first the query selects the two columns named DEPT and HIGHEST SALARY (HIGHEST_SALARY is not the actual column but a column alias of specified expression i.e MAX(SALARY)) from table named EMPLOYEES2,

Secondly, the query will make the unique groups of the dept column, as there are three unique departments (SALES, MANUFACTURING, and ACCOUNTING)

And results in the output with a maximum salary of all three individual groups.

Using GROUP BY clause with AVG AGGREGATE function

As we have learned earlier that AVG aggregate function is used to find the average of numerical values from a specified column

For this, we will consider same above table named EMPLOYEES2 for practices.

Example



Description

The above query will also select the two columns named DEPT and AVG_SALARY (aliased) from table,

Note here we have used WHERE condition and IN operator, to read about SQL operator click here

Now the where condition will limit the search operation and search values for only two departments that are sales and accounting,

Using GROUP BY clause with COUNT AGGREGATE function

Right previous examples, you can operate it similarly, just as you better know that count aggregate function is used to count the total records of a column or entire table

Example



Description

Above example shows the total number of records per group (dept)

Similarly, you can use all other Aggregate functions with Group by clause.