help me querying this

[2mhzbrain]

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

this is tricky for a newbie, please help me,, thanks a lot

[|-|x]

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

[GeoEqual]

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

[|-|x]

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.

[GeoEqual]

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

[imBaCodes]

if hearth help you mark this as solve.

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

[GeoEqual]

thank you for the advice and additional info...