Posts by |-|x

To nest the if() functions, simply place an aditional one inside the true or false return sections like so if(condition,true_1,if(condition2,true_2,false_both)) This would be useful if you had more than two unit types, for example:

if(unit='cm',qty/100,if(unit='mm',qty/1000,qty))

would convert both cm and mm to m. If you have more than this you would be better to use a CASE statement. Have a read of the MySQL docs for more info on both IF and CASE.

In order to pull the units from a different table you would need to join that table based on some key. (This is purely speculative as I don't know what your data structure is.)

SELECT 
    table.item, 
    sum(if(unittable.unit='cm',table.qty/100,table.qty)) qty, 
    'm' unit 
FROM table
LEFT JOIN unittable ON unittable.id = table.unit_id
GROUP BY table.item

However for this purpose you could just as easily use the presumed unit_id value in the IF condition rather than joining as it would be simpler and faster.
Check the MySQL docs for more on joins.

You have to use grouping, and I assume from what it looks like that you are converting the units from cm to m.

try something like this...

SELECT 
    item, 
    sum(if(unit='cm',qty/100,qty)) qty, 
    'm' unit 
FROM tablename
GROUP BY item