SQL Avg Function

SQL Avg Function is used to compute the average records value in a column of specified table.

Understand with Example

The Tutorial illustrate an example from 'SQL Avg Function'. To understand and grasp the example we create a table 'stu' with required fieldnames and datatypes respectively.

Create table:

create table Stu(Id varchar(2), Name varchar(15), 
Class  varchar(10),sub_id varchar(2),marks varchar(3));

Insert records into:

The insert into is used to add the records or rows to the table 'Stu'.

insert into Stu values(1,'Komal',10,1,45);
insert into Stu values(2,'Ajay',10,1,56);
insert into Stu values(3,'Rakesh',10,1,67);

insert into Stu values(1,'Komal',10,2,47);
insert into Stu values(2,'Ajay',10,2,53);
insert into Stu values(3,'Rakesh',10,2,57);

insert into Stu values(1,'Komal',10,3,45);
insert into Stu values(2,'Ajay',10,3,56);
insert into Stu values(3,'Rakesh',10,3,67);

insert into Stu values(1,'Komal',10,4,65);
insert into Stu values(2,'Ajay',10,4,56);

insert into Stu values(1,'Komal',10,5,65);
insert into Stu values(3,'Rakesh',10,5,63);

Stu table:

+------+--------+-------+--------+-------+
| Id   | Name   | Class | sub_id | marks |
+------+--------+-------+--------+-------+
| 1    | Komal  | 10    | 1      | 45    |
| 2    | Ajay   | 10    | 1      | 56    |
| 3    | Rakesh | 10    | 1      | 67    |
| 1    | Komal  | 10    | 2      | 47    |
| 2    | Ajay   | 10    | 2      | 53    |
| 3    | Rakesh | 10    | 2      | 57    |
| 1    | Komal  | 10    | 3      | 45    |
| 2    | Ajay   | 10    | 3      | 56    |
| 3    | Rakesh | 10    | 3      | 67    |
| 1    | Komal  | 10    | 4      | 65    |
| 2    | Ajay   | 10    | 4      | 56    |
| 1    | Komal  | 10    | 5      | 65    |
| 3    | Rakesh | 10    | 5      | 63    |
+------+--------+-------+--------+-------+

Query:

The Query below uses group by clause that returns you the unique average value of marks from table stu.

select id, name,avg(marks) 
from stu group by id;

Result

+------+--------+------------+
| id   | name   | avg(marks) |
+------+--------+------------+
| 1    | Komal  | 53.4       |
| 2    | Ajay   | 55.25      |
| 3    | Rakesh | 63.5       |
+------+--------+------------+
3 rows in set (0.00 sec)

SQL Avg Function

SQL Avg Function

SQL Avg Function

Tutorials