Tutorials

# SQL Avg Syntax

SQL Avg Syntax is used to compute the average of the records in a table.

SQL Avg Syntax is used to compute the average of the records in a table.

# 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)```