Computation with different unit of measurement

I have a table like this:-

            Selling    Sales
Item    SIZE    QTY    Price       U/M
----    ----    ----   -----       -----
AAA      5       10    15.00       PCE
BBB      60      5     5.50        CM
CCC      8       7     12.50       PCE
D开发者_如何学CDD      75      3     6.80        CM

I need to compute the above into this using SQL:-

Item    Sales Value
AAA    150.00 
BBB  1,650.00 
CCC     87.50 
DDD  1,530.00 

The problem is the Sales U/M, some in PCE (per piece) & some in CM (per centimeter).

When it's in PCE, it shd be "Selling Price * Qty" & when it's in CM, it shd be "Selling Price * Size * Qty".

How can I do this ??

Thanks.

select item, amount = case 
  when salesum = 'PCE' 
    then price * qty 
    else price * size * qty
  end
from ...

Use a CASE:

select item,
       case um
           when 'PCE' then qty * price
           when 'CM'  then size * qty * price
       end
from your_table
where um in ('PCE', 'CM');

The where clause might be over doing it a bit but you didn't specify that um could only have those two values and I'm not sure what you want to do for an "else" clause in the case.

I would do something like this.

Select Item, Case when UM='CM' then Price * Qty else Price * Qty * Size end as Value from sellingSales

SELECT item, qty * price AS Sales_Value
  FROM your_table
 WHERE um = 'PCE'
UNION ALL
SELECT item, SIZE * qty * price
  FROM your_table
 WHERE um = 'CM';