CAST to DECIMAL in MySQL
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))
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"
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.