EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL ROUND
Secondary Sidebar
MySQL Tutorial
  • Functions
    • MySQL Function
    • MySQL Aggregate Function
    • MySQL String functions
    • MySQL Date Functions
    • MySQL Window Functions
    • MySQL Math Functions
    • MySQL Boolean
    • Cursor in MySQL
    • Condition in MySQL
    • MySQL BETWEEN
    • Insert in MySQL
    • MySQL IFNULL()
    • MySQL TIMESTAMPDIFF()
    • MySQL COALESCE()
    • MySQL count()
    • MIN() in MySQL
    • MySQL Numeric
    • MySQL field()
    • MySQL FIND_IN_SET()
    • MySQL avg()
    • MySQL MAX() Function
    • MySQL BIN()
    • MySQL Concat
    • MySQL DECODE()
    • MySQL REGEXP_REPLACE()
    • MySQL Asynchronous
    • MySQL innodb_buffer_pool_size
    • MySQL key_buffer_size
    • MySQL TRUNCATE()
    • MySQL ROW_NUMBER()
    • NOT in MySQL
    • MySQL IN Operator
    • LIKE in MySQL
    • ANY in MySQL
    • MySQL NOT IN
    • MySQL CHECK Constraint
    • MySQL DISTINCT
    • MySQL ALL
    • MySQL Union
    • MySQL UNION ALL
    • MySQL EXISTS
    • MySQL ON DELETE CASCADE
    • MySQL REGEXP
    • MySQL Index
    • MySQL Add Index
    • MySQL REINDEX
    • MySQL UNIQUE INDEX
    • MySQL Clustered Index
    • MySQL? InnoDB Cluster
    • Table in MySQL
    • ALTER TABLE MySQL
    • MySQL Temporary Table
    • MySQL Clone Table
    • MySQL Repair Table
    • MySQL Lock Table
    • MySQL Optimize Table
    • TRUNCATE TABLE MySQL
    • MySQL Table Dump
    • MySQL Update Set
    • MySQL ALTER TABLE Add Column
    • MySQL RANK()
    • MySQL CTE
    • MySQL LAG()
    • MySQL GROUP_CONCAT()
    • MySQL EXTRACT()
    • MySQL REPLACE
    • MySQL AUTO_INCREMENT
    • MySQL SYSDATE()
    • MySQL NULLIF()
    • MySQL Substring
    • MySQL SUBSTRING_INDEX()
    • MySQL LOWERCASE
    • MySQL Row
    • MySQL NOW
    • MySQL CEIL
    • MySQL Alias
    • MySQL Trigger
    • MySQL SHOW Triggers
    • MySQL UPDATE Trigger
    • MySQL DELETE Trigger
    • MySQL AFTER UPDATE Trigger
    • MySQL Stored Procedure
    • ROLLUP in MySQL
    • MySQL? INSTR()
    • MySQL Subquery
    • MySQL Timestamp
    • MySQL? Hour()
    • MySQL MOD()
    • MySQL DATE_FORMAT()
    • ALTER Column in MySQL
    • MySQL Rename Column
    • MySQL Interval
    • MySQL CURDATE
    • MySQL BIT
    • MySQL Binlog
    • MySQL Average
    • MySQL TEXT
    • MySQL SHOW
    • MySQL Offset
    • MySQL Timezone
    • mysql_real_escape_string
    • MySQL Datetime
    • MySQL DATE_SUB()
    • MySQL FULLTEXT
    • MySQL DATE_ADD()
    • MySQL sum()
    • MySQL Merge
    • MySQL BigInt
    • MySQL ROUND
    • MySQL VARCHAR
    • MySQL Decimal
    • MySQL Limit
    • MySQL today()
    • MySQL WEEKDAY
    • MySQL Split
    • MySQL Create Function
    • MySQL BLOB
    • MySQL encode()
    • MySQL Primary Key
    • MySQL Foreign Key
    • Unique Key in MySQL
    • MySQL Drop Foreign Key
    • MySQL DROP TRIGGER
    • MYSQL Database
    • Delete Database MySQL
    • MySQL Root
    • MySQL Root Password
    • MySQL Client
    • MySQL Users
    • MySQL?User Permissions
    • MySQL add user
    • MySQL List User
    • MySQL Show Users
    • MySQL User Password
    • MySQL?Cardinality
    • MySQL Workbench
    • MySQL Backup
    • MySQL REVOKE
    • MySQL Dump
    • MySQL Cluster
    • MySQL Partitioning
    • MySQL Full Text Search
    • MySQL Admin Tool
    • MySQL Export Database
    • MySQL Export to CSV
  • Basic
    • MySQL floor
    • MySQL DESCRIBE table
    • MySQL encryption
    • Introduction to MySQL
    • Is SQL Server a Database?
    • What is MySQL
    • Is MySQL Programming Language
    • MySQL Server
    • MySQL Community Server
    • How To Install MySQL
    • MySQL Versions
    • MySQL OpenSource
    • MySQL GUI Tools
    • MySQL Grant
    • MySQL Error 1064
    • MySQL Drop Database
    • MySQL not equal
    • MySQL SELECT INTO Variable
    • MySQL Commands
    • MySQL Operators
    • What is MySQL Schema
    • Wildcards in MySQL
    • MySQL Constraints
    • MySQL Administration
    • MySQL Data Type
    • MYSQL COMMIT
    • MySQL FORMAT
    • Timestamp to Date in MySQL
    • MySQL DATEDIFF
    • MySQL?Incremental Backup
    • MySQL JSON Data Type
    • MySQL ENUM
    • MySQL Default Port
    • Cheat Sheet MySQL
  • Queries
    • MySQL Queries
    • MySQL Query Commands
    • SELECT in MySQL
    • MySQL INSERT IGNORE
    • MySQL having
    • ORDER BY in MySQL
    • MySQL Cheat Sheet
    • MySQL ORDER BY Random
    • MySQL ORDER BY DESC
    • MySQL GROUP BY
    • MySQL GROUP BY Count
    • MySQL GROUP BY month
    • MySQL WHERE Clause
    • MySQL WITH
    • MySQL FETCH
    • MySQL DDL
    • MySQL DML
    • MySQL WHERE IN Array
    • MySQL Fetch Array
    • MySQL ISNULL
    • MySQL Index Types
    • Mysql? Export Schema
    • Amazon RDS for MySQL
    • MySQL greatest
  • Database
    • What is Data Modeling
    • What is Data Processing
    • DBMS Architecture
    • DBMS Keys
    • Careers in Database Administration
    • What is MySQL Database
    • MySQL Relational Database
    • How to Connect Database to MySQL
    • MySQL Database Repair
    • RDBMS Interview Questions
    • DBMS Interview Questions
  • Joins
    • Joins in MySQL
    • MySQL Outer Join
    • Left Outer Join in MySQL
    • MySQL Self Join
    • Natural Join in MySQL
    • MySQL DELETE JOIN
    • MySQL Update Join
    • MySQL Cross Join
  • Advanced
    • MySQL Formatter
    • MySQL TINYINT
    • MySQL Grant All Privileges
    • MySQL DROP TABLE
    • MySQL rename database
    • MySQL Flush Privileges
    • MySQL super Privilege
    • MySQL Character Set
    • MySQL Log File
    • MySQL Flush Log
    • Grant Privileges MySQL
    • MySQL WHILE LOOP
    • IF Statement in MySQL
    • MySQL CASE Statement
    • MySQL IF Function
    • MySQL IF EXISTS
    • MySQL UUID
    • Views in MySQL
    • MySQL Replication
    • MySQL Partition
    • Toad for MySQL
    • Navicat for MySQL
    • MySQL AES_Encrypt
    • MySQL Performance Tuning
    • MySQL Transaction
    • MySQL? sort_buffer_size
    • MySQL? Sync
    • MySQL? Query Cache
    • MySQL Collation
    • MySQL ODBC Driver
    • MySQL Partitioning
    • MySQL InnoDB
    • MySQL Float vs Decimal
    • MySQL Union vs Union All
  • Interview Questions
    • MySQL Interview Questions

Related Courses

MS SQL Certification Courses

Oracle Certification Courses

PL/SQL Certification Courses

MySQL ROUND

By Payal UdhaniPayal Udhani

MySQL ROUND

Introduction to MySQL ROUND

The round function in MySQL is used to round the numerical values in the database to a certain number of decimal places. Whenever we perform any aggregate functions on certain numerical values such as average, division, or product of the numbers then the resultant value may contain the number that is specified in the format that contains its value up to many digits after the decimal point to maintain the precision. Many times, it is required to display such numerical values up to some specific digits after the decimal point or even round the value to the whole integer number. In such cases, we can use the round function of MySQL to retrieve the numerical values in the desired format up to the desired decimal points and round off this value appropriately.

In this article, we will learn about the syntax of the round function and some of the examples that will help us to know how we can use the round function.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (85,992 ratings)

Syntax:

ROUND(number,[decimal]);

In the above syntax of ROUND, function number stands for the numerical value that can be any expression, column value of constant value that you wish to round off. Decimal is the digit that represented the number of places up to which decimal value you have to round the number value. The decimal is the integer value that can be either positive or negative. When specified as a negative value then the number of digits before the decimal point will be made zero and the number will be rounded in the left side flow. Let us understand the working of positive and negative decimal parameter values with the help of an example.

Let us build a query statement that will have the positive integer value specified in the decimal parameter –

SELECT ROUND(454.1234,2);

Execution of the above query gives the following output –

MySQL ROUND 1

Now, let us study the output of the negative integer value as a decimal parameter using the below query –

SELECT ROUND(454.1234,-2);

Execution of the above query gives the following output –

MySQL ROUND 2

We can see from the output that the value is rounded off on the left side of the decimal place. As the decimal places up to which we have to round are -2, two digits from the left are made zero while the third digit depends on the value of the second digit. If the value of the second git is greater than or equivalent to 5 then the value of the third digit is increased by one else it stays the same.

Let us consider one more example of the negative integer value in the decimal parameter of round function by using the following query statement –

SELECT ROUND(444.1234,-2);

Execution of the above query gives the following output –

MySQL ROUND 3

We can observe from the output that the value of the third digit from left remains intact even after rounding to 2 places negatively because the second place contained 4 that is less than 5 value in it.

The value of the decimal parameter when not specified is considered zero. Let us take one example

SELECT ROUND(646.136,0);

Execution of the above query gives the following output –

MySQL ROUND 4

SELECT ROUND(646.136);

Execution of the above query gives the following output –

MySQL ROUND 5

Exact-value Number Rounding

The exact value number rounding makes the use of round to nearest rule that is when the fractional part consists of digit 5 or greater after the digit up to which the value is to be round then the value is rounded up to the next integer value if the number is positive while in case of negative numbers it is rounded down. Similarly, if the fractional part of the digit after the round decimal value is less than 5 then the positive number of rounded down while the negative number is rounded up.

Consider one example to clarify the concept.

If digit after the round value is greater than or equal to 5 –

A positive number is rounded up –

SELECT ROUND(26.5);

Execution of the above query gives the following output –

MySQL ROUND 6

The negative number is rounded down –

SELECT ROUND(-26.5);

Execution of the above query gives the following output –

MySQL ROUND 7

If digit after the round value is less than to 5 –

A positive number is rounded down –

SELECT ROUND(26.4);

Execution of the above query gives the following output –

positive number

The negative number is rounded up –

SELECT ROUND(-26.4);

Execution of the above query gives the following output –

negative numbers

Approximate-value Number Rounding

The rounding of approximate-valued numbers follows the round to nearest even rule and it also depends on the C library. In the round to nearest even rule the number is exactly split in halfway and then depending on the nearest even integer value the number is rounded off.

Example of round function on column values and expressions –

Let us create one table named educba_writers that will contain the following columns mentioned the create table query –

CREATE TABLE `educba_writers` (
`id` int(11) NOT NULL,
`firstName` varchar(10) COLLATE latin1_danish_ci NOT NULL,
`rate` decimal(5,4) DEFAULT NULL,
`joining_date` date DEFAULT NULL,
`article_count` INTEGER DEFAULT 0,
`gst_percent` DECIMAL(5,2) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci;

that gives following output –

round function

Let us insert some records in it –

INSERT INTO `educba_writers` (`id`, `firstName`, `rate`, `joining_date`, `article_count`, `gst_percent`) VALUES
(1, 'Payal', '5.598', '2020-05-01',51,1.35),
(2, 'Vyankatesh', '7.6858', '2020-01-01',65,1.75),
(3, 'Omprakash', '6.6898', '2020-02-01',72,1.55),
(4, 'sakshi', '2.6559', '2020-06-01',49,1.95),
(5, 'prerna', '5.6858', '2020-02-01',72,1.55),
(6, 'preeti', '5.6258', '2020-06-01',49,1.95),
(7, 'sanjana', '4.5498', '2020-02-01',72,1.55),
(8, 'omkar', '4.6528', '2020-06-01',49,1.95),
(9, 'sohail', '6.6518', '2020-02-01',72,1.55),
(10, 'soniya', '8.5298', '2020-06-01',49,1.95),
(11, 'supriya', '7.1659', '2020-02-01',72,1.55),
(12, 'saniya', '7.5198', '2020-06-01',49,1.95),
(13, 'omkar', '4.6358', '2020-02-01',72,1.55),
(14, 'akshay', '9.6458', '2020-06-01',49,1.95),
(15, 'akash', '7.6948', '2020-02-01',72,1.55),
(16, 'siddharth', '8.4659', '2020-06-01',49,1.95);

that gives the following output after execution –

insert some records

Now, we have to calculate the GST amount to be deduced in payment for each of the writers. For this, we will have to calculate the product of rate and number of articles that will be payment amount of articles, and further, the GST to be deducted can be calculated by the product of rate, number of articles, and GST percentage. Hence, our query will be as follows –

SELECT firstName,(rate * article_count * gst_percent) as gst_deducted FROM educba_writers;

insert some records 1

Now, we have to round this GST amount up to two places. So the query will be

SELECT firstName,ROUND((rate * article_count * gst_percent),2) as gst_deducted FROM educba_writers;

that gives following output –

insert some records 2

Conclusion

The ROUND() function is the mathematical function that can be used in MySQL to round off the numerical values of expressions or column values.

Recommended Articles

This is a guide to MySQL ROUND. Here we discuss Introduction, syntax, and Examples with code implementation. You can also go through our other related articles to learn more –

  1. MySQL having
  2. MySQL Data Type
  3. MySQL Export Database
  4. MySQL today()
Popular Course in this category
MySQL Training Program (12 Courses, 10 Projects)
  12 Online Courses |  10 Hands-on Projects |  92+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

MS SQL Training (16 Courses, 11+ Projects)4.9
Oracle Training (14 Courses, 8+ Projects)4.8
PL SQL Training (4 Courses, 2+ Projects)4.7
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more