I use data types decimal(10,3) and now I try to get SUM of all column, and it will return depends on what is the SUM of all units.
SELECT CONCAT(SUM(prod_name,'-',prod_quantity,' ',
CASE WHEN prod_unit = 'mg' THEN 'g'
END)) AS shortName
There are two parts to this question. First, you should add the weights properly, which means adding them all after converting them to the same units. To avoid losing precision, let's add them in milligrams:
SELECT SUM(prod_quantity * CASE prod_unit WHEN 'mg' THEN 1 WHEN 'g' THEN 1000 WHEN 'kg' THEN 1000000 END) sum_milligrams FROM prod_table;
This will give the results in milligrams. Now, we can handle beautifying the output by deciding in which units to display it. For example, we could decide that anything weighing 1 kg or more would be displayed in kg, anything between 1 gr and 1 kg would be displayed in grams and anything less than that would be displayed in milligrams:
SELECT CASE WHEN s > 1000000 THEN CONCAT(s/1000000.0, ' kg') WHEN s BETWEEN 1000 AND 1000000 THEN CONCAT(s/1000, ' g') ELSE CONCAT(s, ' mg') END FROM (SELECT SUM(prod_quantity * CASE prod_unit WHEN 'mg' THEN 1 WHEN 'g' THEN 1000 WHEN 'kg' THEN 1000000 END) s FROM prod_table) t;