This topic explains how to include a calculated column in a query using SQL expressions.
To use the examples on this page, install the
example study downloadable from this topic.
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 pair shows how to use intermediate and related queries to calculate the Body Mass Index (BMI). In this scenario, we have a study where height is stored in a demographic dataset (does not change over time) and weight is recorded in a physical exam dataset. Before adding this BMI calculation, we create an intermediate query "Height and Weight" using the study "Datasets" table alignment to provide weight in kg and height in meters for our calculation.
Height and Weight query:
SELECT PhysicalExam.ParticipantId,
PhysicalExam.date,
PhysicalExam.weight_kg,
TRUNCATE(CAST(Datasets.Demographics.height AS DECIMAL)/100, 2) as height_m,
FROM PhysicalExam
BMI query:
SELECT "Height and Weight".ParticipantId,
"Height and Weight".Date,
"Height and Weight".weight_kg,
"Height and Weight".height_m,
ROUND(weight_kg / (height_m * height_m), 2) AS BMI
FROM "Height and Weight"
Query Key Fields
If you intend to use a custom query in a join, note that you may need to explicitly specify a primary key appropriate to the join. Learn more here:
Query Metadata
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