Aggregate functions – AGGREGATE Concepts With Examples In Oracle

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.

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

1. In the above example, the inner aggregate MAX(salary) is evaluated per each group of department_id.
2. 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?

1. 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.
2. 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 = ( select min(salary)
  from employees
  );

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        132 TJ                         2100

What is happening in the MIN example query?

1. The inner query “select min(salary) from employees” finds the minimum salary available in the “employees” table.
2. 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> select count(*)
  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> select sum(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> select avg(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.