Add a Calculated Column to a Query

Documentation
This topic explains how to add a calculated column to a query using SQL expressions.

Example: Add a Calculated Column

In this example we use SQL to add a column to a query based on the Physical Exam dataset. The column will display "Pulse Pressure" -- the change in blood pressure between contractions of the heart muscle, calculated as the difference between systolic and diastolic blood pressures.

Create a Query

  • Navigate to Admin > Developer Links > Schema Browser.
  • Select a schema to base the query on. (For this example, select the study schema.)
  • Click Create New Query.
  • Create a query based on some dataset. (For this example, select the Physical Exam dataset.)

Modify the SQL Source

  • Adding the following SQL will create a column with the calculated value we seek:
"Physical Exam".SystolicBloodPressure-"Physical Exam".DiastolicBloodPressure as PulsePressure
  • The final SQL source should look like the following:
SELECT "Physical Exam".ParticipantId,
"Physical Exam".date,
"Physical Exam".Weight_kg,
"Physical Exam".Temp_C,
"Physical Exam".SystolicBloodPressure,
"Physical Exam".DiastolicBloodPressure,
"Physical Exam".Pulse,
"Physical Exam".Respirations,
"Physical Exam".Signature,
"Physical Exam".Pregnancy,
"Physical Exam".Language,
"Physical Exam".SystolicBloodPressure-"Physical Exam".DiastolicBloodPressure as PulsePressure
FROM "Physical Exam"
  • Click Save and Finish.
  • Notice that LabKey Server has made a best guess at the correct column label, adding a space to "Pulse Pressure".

Example #2

The following query shows how to calculate the Body Mass Index (BMI) from height and weight columns.

SELECT "Height and Weight".ParticipantId,
"Height and Weight".Date,
"Height and Weight".Gender,
"Height and Weight".Weight_kg,
"Height and Weight".Height_m,
ROUND(Weight_kg / (Height_m * Height_m), 2) AS BMI
FROM "Height and Weight"

Related Topics

Discussion

previousnext
 
expand all collapse all