Skip to content
Advertisement

Converting weight to grams in a SELECT statement

I have a Table called parts which looks like this:

SELECT * FROM parts

PNO   PNAME      COLOR      WEIGHT CITY       PRICE
----- ---------- ---------- ------ ---------- ------
P1    Nut        blue       13     London     7.92
P2    Bolt       green      18     Paris      4.95
P3    Screw      blue       17     Rome       4.95
P4    Screw      red        14     London     6.93
P5    Cam        blue       12     Paris      7.92
P6    Cog        red        19     London     4.95

    6 record(s) selected.

Now I want to select the PNO, color,weight and city for “non-Paris” parts with a weight greater than ten pounds. This is not that difficult:

SELECT PNO, COLOR, WEIGHT, CITY
FROM parts
WHERE CITY != 'Paris' AND WEIGHT > 10;

Now to the part I don’t understand. How can I add a column to my result which shows the weight converted in to grams (multiplied by 454 since one pound equals 0.454 kg). The result should look like this:

PNO   COLOR      WEIGHT GRAMS       CITY
----- ---------- ------ ----------- ----------
P1    blue       13     5902        London
P3    blue       17     7718        Rome
P4    red        14     6356        London
P6    red        19     8626        London

Advertisement

Answer

Just add the extra column in as a calculation with an alias:

SELECT PNO, COLOR, WEIGHT, WEIGHT*454 AS GRAMS, CITY
FROM parts
WHERE CITY != 'Paris' AND WEIGHT > 10;
Advertisement