CAST to DECIMAL in MySQL

Question

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

User · Answer

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

User · Answer

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

User · Answer

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 gt  select 5 into  myvar1  Query OK  1 row affected  0 00 sec   mysql gt  select 8 into  myvar2  Query OK  1 row affected  0 00 sec   mysql gt  select  myvar1  myvar2      prints 0 6250   Cast decimal back to string   select cast 1 552 as char 10        shows  1 552

User · Answer

An alternative  I think for your purpose  is to use the round   function   select round  10   1 5  2     prints 15 00   You can try it here

User · Answer

If you need a lot of decimal numbers  in this example 17  I share with you MySql code   This is the calculate        9 1147  100      SELECT TRUNCATE   CAST 9 AS DECIMAL 30 20   1147  100  17

[mysql] CAST to DECIMAL in MySQL

Examples related to mysql

Examples related to casting

Examples related to decimal