GROUP CONCAT FUNCTION

Round Function in SQL is used to round a numeric field to the nearest decimals specified.

Understand with Example

The Tutorial illustrates an example from SQL Round Function. In this Tutorial, we create a table 'Stu_Table'. 

create table :The create table is used to create a table Stu_Table.

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 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(3,'Rakesh',10,4,37); insert into Stu_Table values(1,'Komal',10,5,65); insert into Stu_Table values(2,'Ajay',10,5,46); 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 | | 3 | Rakesh | 10 | 4 | 37 | | 1 | Komal | 10 | 5 | 65 | | 2 | Ajay | 10 | 5 | 46 | | 3 | Rakesh | 10 | 5 | 63 | +--------+----------+-----------+--------+-------+

Query

The Query Syntax show you the (avg(marks)) rounded to the nearest integer.

select stu_id, stu_name, GROUP_CONCAT(marks) as marks, sum(marks)as total ,avg(marks) as per, round(avg(marks)) as round_per from stu_table group by stu_id;

Result

+--------+----------+----------------+-------+------+-----------+ | stu_id | stu_name | marks | total | per | round_per | +--------+----------+----------------+-------+------+-----------+ | 1 | Komal | 45,65,47,65,45 | 267 | 53.4 | 53 | | 2 | Ajay | 46,56,56,53,56 | 267 | 53.4 | 53 | | 3 | Rakesh | 67,57,37,67,63 | 291 | 58.2 | 58 | +--------+----------+----------------+-------+------+-----------+