SQL Avg Syntax
SQL Avg Syntax is used to compute the average of the records in a table.
Understand with Example
The Tutorial illustrate an example from 'SQL Average Syntax'. To understand and grasp example we create a table 'Stu' with required fieldnames and datatypes required.
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 | +------+--------+-------+--------+-------+ |
Syntax
The Syntax below is used to return the records and average value of the records specified in the column
Select column_names,avg(column_name) from table_name group by column_name |
Query:
The Query return the records and average value of marks from table 'stu'and the group by clause sorts the unique record by column name id.
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) |