Home Sql SQL ROUND Function

SQL ROUND Function
Posted on: December 20, 2008 at 12:00 AM

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

SQL ROUND 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        |
+--------+----------+----------------+-------+------+-----------+

Related Tags for SQL ROUND Function:
sql, c, rest, function, fun, io, sed, decimal, field, if, ie, unc, numeric, to, round, ld, ci, ear, e, ls, use, pe, im, decimals, in, m, es, spec, func, me, s, sp, is, ea, ar, s, s, ri, th, st, nd, on, o

«Previous Index Next»