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.

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.

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

 emp_id first_name last_name dept_id salary 1 Rahul Roy 101 60,000 2 Pritam Halder 102 40,000 3 Ritika Sen 102 ? 4 Sagar Sharma 101 40,000

### 1) Teradata Average using ALL

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

Output

 Average(salary) 46,666.67

### 2) Teradata Average using DISTINCT

`select avg(distinct salary) from employee;`

Output

 Average(Distinct(salary)) 50,000.00

### 3) Average with the group by clause

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

Output

 dept_id Average(salary) 101 50,000.00 102 40,000.00