``````this is the table
ITEM    QTY     UNIT
rod1    50      cm
rod1    1       m
rod1    50      cm
rod2    25      cm
rod2    75      cm
rod2    1       m

this is the result i want user can see
ITEM    QTY     UNIT
rod1    2       m
rod2    2       m
``````

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
``````

this worked perfectly, thanks a lot
can you please explain to me these 2 lines on your equation:

sum(if(unit='cm',qty/100,qty)) qty,
'm' unit

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`

Hope that explains everything.

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:

``````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.