Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MySQL decimal field is rounding and shouldn't be

Posted on 2016-09-06
12
Medium Priority
?
64 Views
Last Modified: 2016-09-06
Hi there! I have a MySQL table that includes the following table column:

	percentDegree	decimal(3,1)	Not NULL	Default 0.0

Open in new window


When I try to run an update...

UPDATE tbl_degrees SET percentDegree = '76.9' WHERE dataDate = '2015'

Open in new window


...the 76.9 is entered in the table as 77.0.

I've seen other questions like this on here but it's usually a matter of people not having and decimal places defined or using the INT type. I've also seen a comment that "you can use FLOAT, but that comes with its own problems" but can't seem to find what those problems would be. Also, isn't this exactly the data type you would use decimals for?

Thanks!
0
Comment
Question by:SiobhanElara
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 41786176
what about:

UPDATE tbl_degrees SET percentDegree = 76.9 WHERE dataDate = '2015'

?
1
 
LVL 22

Expert Comment

by:Kim Walker
ID: 41786192
How are you viewing the value that's stored in the table? Are you querying the table or are you browsing the table in phpMyAdmin or a similar MySQL management utility?

If you're querying the table, it's possible that it's getting rounded somehow before it's displayed.
0
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 41786193
>>the 76.9 is entered in the table as 77.0
I just did a test, both SET percentDegree = '76.9' or SET percentDegree = 76.9 are working fine. they didn't turn to 77.0

more towards a display issue for me?
0
Google Certified Professional - Cloud Architect

This course (1 of 3) is designed to help students who are interested in Google Cloud Platform (GCP) to become familiar with the platform, navigate the console and learn its capabilities. It will also prepare students for the Google Cloud Architect certification exam.

 
LVL 59

Expert Comment

by:Julian Hansen
ID: 41786255
Confirm both '76.9' and 76.9 on a decimal(3,1) works as expected.

Where are you seeing 77.0
0
 

Author Comment

by:SiobhanElara
ID: 41786270
It's both displaying that way and in the database that way; I'm also viewing the table through phpMyAdmin. Here's a screenshot of a query, the form, and what's in the database. (I'd entered 26.7 as seen in the query; the area under that is the result. This is a slightly different query than the original question, but all tables are having this problem.)

decimal-wonkyness.jpg
0
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 41786291
And if you run this directly in PHPMyAdmin
UPDATE data_educational_attainment SET percentBachelor=26.7 WHERE countryID=0 AND dataDate='2016';

Open in new window

0
 

Author Comment

by:SiobhanElara
ID: 41786307
That works, as does double-clicking on the cell on phpMYAdmin table view and entering it there.
0
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 41786316
The problem is then in how you are submitting it to the DB - what is running that query?
0
 

Author Comment

by:SiobhanElara
ID: 41786350
The ColdFusion code is as follows:

<cfquery datasource="#APPLICATION.ds#" result="bloop">
UPDATE #FORM.tableName#
SET #FORM.dataName# = <cfqueryparam value="#FORM[i]#" cfsqltype="cf_sql_decimal">
WHERE countyID = <cfqueryparam value="#countyID#" cfsqltype="cf_sql_integer">
</cfquery>

Open in new window


("Bloop" outputs the query shown in the screenshot above.)
0
 
LVL 59

Accepted Solution

by:
Julian Hansen earned 2000 total points
ID: 41786366
And if you do this
<cfquery datasource="#APPLICATION.ds#" result="bloop">
UPDATE #FORM.tableName#
SET #FORM.dataName# = <cfqueryparam value="#FORM[i]#" scale="1" cfsqltype="cf_sql_decimal">
WHERE countyID = <cfqueryparam value="#countyID#" cfsqltype="cf_sql_integer">
</cfquery>

Open in new window

I don't use ColdFusion but according to the docs - the omission of the scale parameter defaults the value to 0 decimal places.
0
 

Author Closing Comment

by:SiobhanElara
ID: 41786416
Holy crap, I hang my head in shame. Thank you for pointing out what I should have known all along!
0
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 41786467
You are welcome.
0

Featured Post

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question