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 Available

Subscribers 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

Was this content helpful?

Log in or register an account to provide feedback


previousnext
 
expand allcollapse all