SQL Average Count

Average Count in SQL is used to count the aggregate sum of any field in a table.

Understand with Example

The Tutorial illustrate an simple demonstrative example ,which helps you to calculate the average count of any records specified in field. The create table help you to create a table 'Stu_Table' with table attribute like field name,data type etc.

Create Table Stu_Table

SQL statement to create table:

create table Stu_Table(Stu_Id varchar(2), Stu_Name varchar(15), 
Stu_Class  varchar(10),sub_id varchar(2),marks varchar(3));

Insert Data into Stu_Table

The insert into add the records or rows to the created table 'Stu_Table'.

SQL statement to insert data into table:

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

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

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

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

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

Stu_Table

Records in the table:

+--------+----------+-----------+--------+-------+
| Stu_Id | Stu_Name | Stu_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 given below Query return you the records from table 'Stu_Table' and aggregate count for the table field 'stu_name'. The Group By clause in this Query is used with aggregate functions and also specifies the group 'stu_id' where selected rows are placed.

select stu_id, stu_name,
count(stu_name) from stu_table group by stu_id;

Result

+--------+----------+-----------------+
| stu_id | stu_name | count(stu_name) |
+--------+----------+-----------------+
| 1      | Komal    | 5               |
| 2      | Ajay     | 4               |
| 3      | Rakesh   | 4               |
+--------+----------+-----------------+

SQL Average Count

SQL Average Count

SQL Average Count

Tutorials