CAST to DECIMAL in MySQL
Solution 1
From MySQL docs: Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC:
In standard SQL, the syntax
DECIMAL(M)
is equivalent toDECIMAL(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))
Solution 2
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"
Solution 3
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.
Solution 4
I'm lazy:
0 + colname
Furthermore, this works for fixed with any number of decimal places, float, etc.
See also ROUND()
and FORMAT()
.
Ben
Updated on December 30, 2021Comments
-
Ben over 2 years
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?
-
Ben almost 12 yearsThanks, 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'
-
ypercubeᵀᴹ almost 12 years@Ben: Don't use single quotes there:
AS 'Cost'
. Use:AS Cost
-
Ben almost 12 yearsThanks, but I'm still getting the error when I remove the quotes.