Posted by Hilarion on 08/24/05 13:45

> Hello, I have a table with a single column "WEIGHT" which is an
> unsigned integer meant to represent weight in ounces. Right now, I
> have 4 values in this table (4, 8, 16, 32). When I select values from
> this table, I would like to be able to display like this:
>
> 4 oz
> 8 oz
> 1 lb
> 2 lb
>
> How can I write a query to make this happen?


Maybe something like that (should work in MySQL):

SELECT CASE
WHEN weight IS NULL THEN NULL
WHEN weight < 16 THEN CONCAT( weight , ' oz' )
WHEN weight < (16*14) THEN CONCAT( weight / 16 , ' lb' )
WHEN weight < (16*14*2) THEN CONCAT( weight / (16*14) , ' st' )
WHEN weight < (16*14*2*4) THEN CONCAT( weight / (16*14*2) , ' qtr' )
WHEN weight < (16*14*2*4*20) THEN CONCAT( weight / (16*14*2*4) , ' cwt' )
ELSE CONCAT( weight / (16*14*2*4*20), ' ton' )
END
FROM weights_table
ORDER BY weight


Hilarion

[Back to original message]