How to convert UNIT of MEASURE in SQL SERVER OR ORACLE USING CASE STATEMENT

Question

Hi,

Is there a way in SQL SERVER or ORACLE that can use the case statment to convert the Unit of Measure below?

select UoM, sum(Quantity) as 'QTY'
from mytable
group by UoM


UoM QTY
LBS 2.4
LBS 2
LBS 0.233
LBS 0.97
OZS 1.8
GMS 1236
LBS 120.459
LBS 59.1
LBS 252.82
LBS 175.23
LBS 3.42
LBS 455.4
LBS 57.6
LBS 146.8
LBS 117.78
LBS 197.92
LBS 40.245
GMS 9
LBS 15.78
LBS 12.6
LBS 125.1
LBS 42.3
LBS 1292.3

1 pound = 16 ounces (OZS) 1 pound = 453.5924 gram (GMS)

Answer 1

I suppose you could do something like

SELECT sum( (case when UoM = 'LBS' then quantity
                  when UoM = 'OZS' then quantity/16
                  when UoM = 'GMS' then quantity/453.5924
                  else null
               end) ) weight_in_lbs,
       sum( (case when UoM = 'LBS' then quantity*16
                  when UoM = 'OZS' then quantity
                  when UoM = 'GMS' then quantity*16/453.5924
                  else null
               end) ) weight_in_ozs,
       sum( (case when UoM = 'LBS' then quantity*453.5924
                  when UoM = 'OZS' then quantity*453.5924/16
                  when UoM = 'GMS' then quantity
              end) ) weight_in_gms
  FROM myTable

Answer 2

Are you just looking for a single grand total? Or a subtotal of each different measure? Or the same total expressed in 3 different measures?

SELECT SUM(CASE UoM
       WHEN 'GMS' THEN Quantity
       WHEN 'LBS' THEN Quantity * 453.5924
       WHEN 'GMS' THEN Quantity * 453.5924 / 16
       END) AS TOTALGrams
FROM myTable

Should give you the grand total in Grams.