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 with the name of your choice, based on some dataset. (For this example, select the Physical Exam dataset.)
- Click Create and Edit Source.
Modify the SQL Source
The default query provided simply selects all the columns from the chosen dataset. Adding the following SQL will create a column with the calculated value we seek. Subtract the diastolic from the systolic to calculate pulse pressure:
"Physical Exam".SystolicBloodPressure-"Physical Exam".DiastolicBloodPressure as PulsePressure
The final SQL source should look like the following, with the new line added at the bottom. remember to add a comma on the previous line:
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".
To reopen and further edit your query, return to the schema browser, either using the "study Schema" link at top of the page or the
menu. Your new query will be listed in the study schema under
user-defined queries.
Example #2
The following query shows how to calculate the Body Mass Index (BMI) from height and weight columns in a dataset named "Height and Weight".
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
