the sum() function simply calculates the total for the group of whatever field you give it, so if you just said sum(qty) you would end up with 101 for rod1.
The if function is where we do the conversion, and it operates just like any typical if statement: if(condition,true_part,false_part) so we are saying if the unit is 'cm' return qty/100 (convert to m) else just return qty. You could nest these, or use a case statement if you had more than two unit types.
The qty at the end is the alias for the column, otherwise the column header would show the formula - this is the same as saying sum(..) as qty (the as is optional).
Since we are grouping we can't just select the unit column as it wont necessarily pull the correct row, and since we are converting all units to m, we can hard code this value in the column. Therefore 'm' unit is simply returning the hard coded string 'm' for all rows as a column named unit
your explanation is very clear, can you show me a nested if inside a query, it looks very interesting to program inside a query, can you give me some example, like query within a query because the unit of measure will be taken from another table
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:
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.)
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.