SQL Average

SQL Average, the part of Aggregate Function. SQL Average is used to compute average value of the records in a table of the database.

Understand with Example

The Tutorial illustrate an example from 'SQL Average'. To grasp and understand example we create a table 'stu' that has the 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:

To add the value into the table 'Stu' we use insert into query.

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 is used to return the id, name and average value of the marks records from table stu. The Group by keyword in SQL is used to sort the data uniquely on the basis of id records.

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 Average

SQL Average

SQL Average

Tutorials