SQL query to calculate a BMI to 2 decimal places using height in inches in varchar and weight in pounds in varchar

Question

I'm trying to write an Amazon Redshift SQL query to calculate body mass index bmi using height in inches in varchar and weight in pounds in varchar to 2 decimal places. I have this which is not working:

SELECT ID,
       CAST(height_in AS decimal),
       CAST(weight_lbs AS decimal),
       CAST(CAST(weight_lbs AS decimal(4, 2))/ (CAST(height_in AS decimal(4, 2)) * CAST(height_in AS decimal(4, 2))) * 703 AS decimal(4, 2)) AS calc_bmi
FROM table
WHERE
 table.ID IN ('a', 'b');

Thank you!

Answer 1

Try this with decimal(6,2) as I already stated in the comment. Plus your query missed one pair parentheses.

(weight / (height * height)) * 703

Instead of:

weight / (height * height) * 703

Since the latter will first multiply (height² * 703), which is not what you want.

Your query should look like this:

SELECT ID,
        CAST(height_in AS decimal),
        CAST(weight_lbs AS decimal),
        CAST((CAST(weight_lbs AS decimal(6, 2))/ (CAST(height_in AS decimal(6, 2)) * CAST(height_in AS decimal(6, 2)))) * 703 AS decimal(6, 2)) AS calc_bmi
FROM table
WHERE
table.ID IN ('a', 'b');

EDIT: it is vital that with these queries your database actually contains values for height_in and weight_lbs, otherwise the CAST operations will trow errors.