Teradata AVG (Average) Function

The average function in Teradata returns the arithmetic average of all values in value_expression. This is generally used to calculate the average value from a specified column.

Teradata average function syntax

The syntax of the Teradata average function is as follows.

AVERAGE (DISTINCT/ALL value_expression)

Here,

• DISTINCT – Optional. If DISTINCT is used in the average function then all the duplicates and null values will be eliminated while calculating the average.
• ALL – Optional. In this case, all the duplicates and null values will be counted while calculating the average.
• value_expression – a literal or column expression for which an average is to be computed.

Teradata average function example

In the following example, we will see the uses of the average function in Teradata.

Sample table and data preparation

Let’s create a volatile table name employee and insert some dummy data into it.

CREATE VOLATILE TABLE employee
(
emp_id INT NOT NULL,
first_name varchar(50) NOT NULL,
last_name varchar(50) NOT NULL,
dept_id INT,
salary INT
)
unique primary index(emp_id)
on commit preserve rows;

insert into employee values (1,'Rahul','Roy',101,60000);
insert into employee values (2,'Pritam','Halder',102,40000);
insert into employee values (3,'Ritika','Sen',102,NULL);
insert into employee values (4,'Sagar','Sharma',101,40000);

Now check the data of the table using the select statement.

select * from employee order by 1;

Output

Different variations of the average function in Teradata

1) Teradata Average using ALL 

select avg(salary) from employee;
or
select avg(ALL salary) from employee;

Output

2) Teradata Average using DISTINCT

select avg(distinct salary) from employee;

Output

3) Average with the group by clause

select dept_id,avg(salary) from employee
group by dept_id
order by dept_id;

Output