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?

Answer 1

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))  

Answer 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"