Arnold Zurbito Arnold Zurbito - 5 months ago 25
MySQL Question

Getting Sum of milligram, grams, and kilograms in MYSQL

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.

E.g.:

0.789 mg
98.057 g
5.008 kg


The query are automatically calculate this, but how?

I've tried this:

SELECT CONCAT(SUM(prod_name,'-',prod_quantity,' ',
CASE WHEN prod_unit = 'mg' THEN 'g'
ELSE prod_unit
END)) AS shortName
FROM prod_table;


the answer is wrong, and I don't know what is the formula on adding mass or volume.

Answer

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;