casting a floating to decimal in mysql

Question

I am not quite understanding the cast function and decimal here. What does decimal(7,2) actually mean?. The first part and the second part arg of the function.

Why do i need to cast/convert my floating point to decimal here?.

mysql> SELECT productCode, MAX(price), MIN(price),
              CAST(AVG(price) AS DECIMAL(7,2)) AS `Average`,
              CAST(STD(price) AS DECIMAL(7,2)) AS `Std Dev`,
              SUM(quantity)
       FROM products
       GROUP BY productCode;
+-------------+------------+------------+---------+---------+---------------+
| productCode | MAX(price) | MIN(price) | Average | Std Dev | SUM(quantity) |
+-------------+------------+------------+---------+---------+---------------+
| PEC         |       0.49 |       0.48 |    0.49 |    0.01 |         18000 |
| PEN         |       1.25 |       1.23 |    1.24 |    0.01 |         15000 |
+-------------+------------+------------+---------+---------+---------------+

Below is a sql fiddle for the same?.

http://sqlfiddle.com/#!2/1ed51b/1/0

My Questions again repeated:

1. What does decimal(7,2) actually mean?. The first part and the second part arg of the function.
2. Why do i need to cast/convert my floating point to decimal here?.

Answer 1

DECIMAL(7,2) means a number with a fixed decimal point, having 7 digits altogether, with 2 of them to the right of the decimal point. (So, 5 left, 2 right.)

You don't need to cast unless you want the behavior of DECIMAL types (typically, fixed point) rather than the behavior of FLOAT types (useful approximations with a relatively unpredictable number of digits right of the decimal point). For example, your average for product code "PEC" with the cast is 33333.65; without the cast it's 33333.653333.

If you're routinely using all the digits, you should probably increase the total number of digits in the cast. Say, to something like DECIMAL (14,2).

Answer 2

Why? Because if the intention is just to display the values, then casting to a DECIMAL with two decimal places will make it easier to read. If you take out the CASTs you will be shown values like AVERAGE 33333.653333 STD DEV 47140.218734.

Answer 3

As you can read on MySQL manual:

The declaration syntax for a DECIMAL column is DECIMAL(M,D). The ranges of values for the arguments in MySQL 5.1 are as follows:

M is the maximum number of digits (the precision). It has a range of 1 to 65. (Older versions of MySQL permitted a range of 1 to 254.)

D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M.

If you use decimal(M,D) you are reserving no places for anything to the left of the decimal. Decimal(7,2) means 7-2 = 5 where 5 is your integer left and 2 being on right.

In simple words, it describes how many total digits a field will be able to store. Decimal(50,2) = 52 total digits, 50 before and 2 after decimal separator.