IT_Expert/DataBase

MySQL] 형변환 Cast Decimal

낫기법필 2016. 4. 4. 23:30

[쥔장]=================================================================

mysql의 double 형과 decimal 간의 값 차이가 발생하는 것을 확인하고, double형을 decimal 형으로 형변환을 했다.

형변환하는 것이야 cast 함수를 사용하면 되지만, decimal로 했을 때의 사용에 대해 조금 더 설명을 한 것이 있어 참고한다.

=======================================================================


[http://stackoverflow.com/questions/11830509/cast-to-decimal-in-mysql]


up vote8down votefavorite
2

I am trying to cast to Decimal in MySQL like this:

CAST((COUNT(*) * 1.5) AS DECIMAL(2))

I'm trying to convert the number of rows in a table (times 1.5) to a floating point number with two digits after the point.

SQL code:

 SELECT CONCAT(Guardian.title, ' ', 
               Guardian.forename, ' ', 
               Guardian.surname) AS 'Guardian Name', 
               COUNT(*) AS 'Number of Activities', 
               (COUNT(*) * 1.5) AS 'Cost'
 FROM Schedule
 INNER JOIN Child ON Schedule.child_id = Child.id
 INNER JOIN Guardian ON Child.guardian = Guardian.id
 GROUP BY Guardian
 ORDER BY Guardian.surname, Guardian.forename ASC

It produces an error:

#1064 - You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use 
near 'CAST((COUNT(*) * 1.5) AS DECIMAL(12,2))' at line 1.

Another try, this cast also doesn't work:

 SELECT CONCAT(Guardian.title, ' ', 
               Guardian.forename, ' ', 
               Guardian.surname) AS 'Guardian Name', 
               COUNT(*) AS 'Number of Activities', 
               CAST((COUNT(*) * 1.5) AS DECIMAL(8,2)) AS 'Cost'
 FROM Schedule
 INNER JOIN Child ON Schedule.child_id = Child.id
 INNER JOIN Guardian ON Child.guardian = Guardian.id
 GROUP BY Guardian
 ORDER BY Guardian.surname, Guardian.forename ASC

How do I use mysql to cast from integer to decimal?

  
shareimprove this question
1 
What kind of trouble? Do you have a specific error message or result that shows the problem? – Holger Brandt Aug 6 '12 at 14:48
   
Try DECIMAL(12,2) – ypercubeᵀᴹ Aug 6 '12 at 14:52
   
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CAST((COUNT(*) * 1.5) AS DECIMAL(12,2))' at line 1. – Ben Aug 6 '12 at 14:59 
   
I've looked at the documentation and can't see why my code isn't working :S – Ben Aug 6 '12 at 14:59
   
Show us the whole code, not just a line. This SQL-Fiddle works fine. – ypercubeᵀᴹ Aug 6 '12 at 15:07 
up vote8down vote

From MySQL docs: Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC:

In standard SQL, the syntax DECIMAL(M) is equivalent to DECIMAL(M,0)

So, you are converting to a number with 2 integer digits and 0 decimal digits. Try this instead:

CAST((COUNT(*) * 1.5) AS DECIMAL(12,2))
shareimprove this answer
   
Thanks, although I've tried that already and just got the following error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECIMAL(12,2)) AS 'Cost' – Ben Aug 6 '12 at 15:02
   
@Ben: Don't use single quotes there: AS 'Cost'. Use: AS Cost – ypercubeᵀᴹ Aug 6 '12 at 15:05
   
Thanks, but I'm still getting the error when I remove the quotes. – Ben Aug 6 '12 at 15:17
up vote5down vote

MySQL casts to Decimal:

Cast bare integer to decimal:

select cast(9 as decimal(4,2));       //prints 9.00

Cast Integers 8/5 to decimal:

select cast(8/5 as decimal(11,4));    //prints 1.6000

Cast string to decimal:

select cast(".885" as decimal(11,3));   //prints 0.885

Cast two int variables into a decimal

mysql> select 5 into @myvar1;
Query OK, 1 row affected (0.00 sec)

mysql> select 8 into @myvar2;
Query OK, 1 row affected (0.00 sec)

mysql> select @myvar1/@myvar2;   //prints 0.6250

Cast decimal back to string:

select cast(1.552 as char(10));   //shows "1.552"
shareimprove this answer
up vote2down vote

DECIMAL has two parts: Precision and Scale. So part of your query will look like this:

CAST((COUNT(*) * 1.5) AS DECIMAL(8,2))

Precision represents the number of significant digits that are stored for values. 
Scale represents the number of digits that can be stored following the decimal point.

shareimprove this answer