**Aggregate functions** are generally useful to **calculate a single row result out of a group of rows**. These functions take more than one rows as input and return a single row as output.

Here are some important things to keep in mind on aggregate functions.

- The aggregate functions return a single row for a group of rows.
- They can be used in select, order by, having clauses.
- Most of the time you will see the aggregate functions are present with the group by clause.

This page contains (Skip to topic)..

## How does the GROUP BY clause work with AGGREGATE Functions?

The Oracle GROUP BY clause divides the result-set into groups. Now, the aggregate functions return one row per group.

So, if we omit the GROUP BY clause and use any aggregate function, it will take the whole result-set of Oracle SELECT Query as a single group.

Let us say we want to count the number of rows of the “student_t” table.

SELECT COUNT(*) FROM student_t;

## How does HAVING work with the AGGREGATE Functions?

We can not use the aggregate functions in the where clause to filter the result-set based on the aggregate functions. This is because these functions do not work for an individual row. Oracle provides **HAVING** clause to write conditional expressions using the aggregate functions in the select query with **group by clause**.

## What are the arguments or parameters of aggregate functions?

Most of the aggregate functions accept one argument. The functions that accept one argument allow the usage of **DISTINCT** and **UNIQUE** with the input argument.

Here is the explanation for the same.

- DISTINCT or UNIQUE keyword selects only distinct or unique value of the input column.
- On contrary, the ALL keyword tells Oracle to select all the values including duplicates.

Example:

SQL> select avg(distinct num) from ( select 1 as num from dual union all select 1 as num from dual union all select 1 as num from dual union all select 3 as num from dual ); AVG(DISTINCTNUM) ---------------- 2 SQL>select avg(all num) from ( select 1 as num from dual union all select 1 as num from dual union all select 1 as num from dual union all select 3 as num from dual ); AVG(ALLNUM) ----------- 1.5 SQL> create table test_t (num number); Table TEST_T created. SQL> insert into test_t values (1); 1 row inserted. SQL> insert into test_t values (1); 1 row inserted. SQL> insert into test_t values (1); 1 row inserted. SQL> insert into test_t values (2); 1 row inserted. SQL> commit; Commit complete. SQL> select num from test_t; NUM ---------- 1 1 1 2 SQL> select avg(num) "default avg" , avg(distinct num) "distinct num avg" , avg(all num) "all num avg" from test_t; default avg distinct num avg all num avg ----------- ---------------- ----------- 1.25 1.5 1.25

## How do the aggregate functions work with NULL?

- All aggregate functions ignore NULL except COUNT(*), GROUPING, and GROUPING_ID.
- Generally, an NVL is used to handle NULL values.
- There are two functions which never return NULL. These are COUNT and REGR_COUNT. These functions return either a number or zero.
- For the rest of the functions, if the input has no rows or has one row with the NULL value, the result of the aggregate function becomes NULL.

## Extra concepts on the aggregate functions

We can use the FIRST or LAST function to operate on a set of values from a set of rows that rank as the first or last with respect to the given sort used in the query. These functions can be used with MIN, MAX, SUM, AVG, COUNT, VARIANCE, and STDDEV when followed by the KEEP keyword.

The aggregate functions can be nested. That means the output of one aggregate functions becomes the input of the other. Here is an example that shows the same,

SELECT AVG(MAX(salary)) FROM employees GROUP BY department_id; AVG(MAX(SALARY)) ---------------- 10926.3333

- In the above example, the inner aggregate MAX(salary) is evaluated per each group of department_id.
- Then, the result of MAX is aggregated by AVG function again.

Now that we knew AGGREGATE functions concepts, we will see how to use group by clause, the having clause, and the following functions.

- MAX finds the maximum value of each group.
- MIN finds the minimum value of each group.
- AVG aggregates and evaluates the average value of each group.
- COUNT evaluates the row count of each group.
- SUM calculates the summation of values per group.

So let us go through each of the function with examples.

### MAX() Function Example

Let us take the employees table into account.

We want to find the employee details and its salary who has the maximum salary out of all the employees. We will use MAX aggregate function to achieve this.

SQL> select employee_id , first_name , salary from employees where salary = ( select max(salary) from employees ); EMPLOYEE_ID FIRST_NAME SALARY ----------- -------------------- ---------- 100 Steven 24000

#### What is happening in the MAX example query?

- The inner query “select max(salary) from employees” finds the maximum salary available in the “employees” table. The use of select statement as the inner query is also known as the subquery.
- The outer query then finds the employee whose salary is equal to max salary. So, this way we get the employee details with the maximum salary with oracle MAX() function.

### MIN() Function Example

Now, let us find the employee details who has the minimum salary. We will use Oracle MIN aggregate function to achieve this.

SQL> select employee_id , first_name , salary from employees where salary = ( selectmin(salary) from employees ); EMPLOYEE_ID FIRST_NAME SALARY ----------- -------------------- ---------- 132 TJ 2100

#### What is happening in the MIN example query?

- The inner query “select
**min**(salary) from employees” finds the minimum salary available in the “employees” table. - The outer query then finds the employee whose salary is equal to min salary. So, this way we get the employee details with the minimum salary with
**oracle MIN() function**.

We can also find the minimum and maximum salary of each department. Here is the query and its output below.

SQL> select job_id ,min(salary) as min_salary ,max(salary) as max_salary , department_id from employees group by job_id , department_id; JOB_ID MIN_SALARY MAX_SALARY DEPARTMENT_ID ---------- ---------- ---------- ------------- IT_PROG 4200 9000 60 FI_MGR 12008 12008 100 PU_MAN 11000 11000 30 ST_MAN 5800 8200 50 SA_REP 6100 11500 80 AD_VP 17000 17000 90 AD_ASST 4400 4400 10 MK_MAN 13000 13000 20 MK_REP 6000 6000 20 HR_REP 6500 6500 40 PU_CLERK 2500 3100 30 SA_MAN 10500 14000 80 SA_REP 7000 7000 SH_CLERK 2500 4200 50 FI_ACCOUNT 6900 9000 100 PR_REP 10000 10000 70 AD_PRES 24000 24000 90 ST_CLERK 2100 3600 50 AC_MGR 12008 12008 110 AC_ACCOUNT 8300 8300 110 20 rows selected.

### COUNT() Function Example

The count function counts the number of rows in the table. We can use it with or without group by clause. If it is used without group by clause then it counts all the rows of the resultset.

SQL> selectcount(*) from user_objects; COUNT(*) ---------- 45

The above query counted the number of rows in the user_objects table in oracle hr schema.

### SUM() Function Example

We will see two examples in this. First of all, let us find out the total salary amount company has to pay. We can do that by aggregating all the employees salary and then finding SUM.

SQL> selectsum(salary) "Total Salary" from employees; Total Salary ------------ 691416

You know, you can also find the summation of salary per department and how many employees receiving part of that sum. Let us see the following Query for the same.

SQL> select department_id , sum(salary) ,count(1)"Number of employees" from employees group by department_id; DEPARTMENT_ID SUM(SALARY) Number of employees ------------- ----------- ------------------- 100 51608 6 30 24900 6 7000 1 90 58000 3 20 19000 2 70 10000 1 110 20308 2 50 156400 45 80 304500 34 40 6500 1 60 28800 5 10 4400 1 12 rows selected.

### AVG() Function Example

Just like the above SUM, COUNT, MAX, MIN aggregate functions, we can find the average of all the values in the salary column either for whole table or group by a certain column.

SQL> selectavg(salary) "Average Salary" from employees; Average Salary -------------- 6461.83178 SQL> select department_id ,avg(salary) "Average Salary Per Department" from employees group by department_id; DEPARTMENT_ID Average Salary Per Department ------------- ----------------------------- 100 8601.33333 30 4150 7000 90 19333.3333 20 9500 70 10000 110 10154 50 3475.55556 80 8955.88235 40 6500 60 5760 10 4400 12 rows selected.

So, In this section, we learned about aggregate function concepts. We then saw some examples with the group by clause. As a bonus, we saw MIN, MAX, COUNT, SUM, AVG usages with oracle queries.

## Your turn now. Please share your questions or feedback. :-)