This topic explains how to add a calculated column to a query using SQL expressions.
Example #1: Pulse Pressure
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) > Go To Module > Query.
- 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. Note that if your query name included a space, you would put double quotes around it, like "Physical Exam". Subtract the diastolic from the systolic to calculate pulse pressure:
PhysicalExam.systolicBP-PhysicalExam.diastolicBP 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 PhysicalExam.ParticipantId,
PhysicalExam.date,
PhysicalExam.weight_kg,
PhysicalExam.temperature_C,
PhysicalExam.systolicBP,
PhysicalExam.diastolicBP,
PhysicalExam.pulse,
PhysicalExam.systolicBP-PhysicalExam.diastolicBP as PulsePressure
FROM PhysicalExam
- 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 near the top of the page or the
menu. Your new query will be listed in the study schema alphabetically.
Example #2: BMI
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"
Display the Calculated Column in the Original Table
Premium Resource AvailableSubscribers to premium editions of LabKey Server can learn how to display a calculated column from a query in the original table following this topic:
Learn more about premium editions
Related Topics
