Creating a
custom SQL query gives you the ability to flexibly present the data in a table in any way you wish using SQL features like calculated columns, aggregation, formatting, filtering, joins, and lookups.
The following steps guide you through creating a custom SQL query and view on a data table.
Create a Custom SQL Query
- Select (Admin) > Go To Module > Query.
- From the schema list, select the schema that includes your data table of interest.
- Optionally select the table on which you want to base the new query.
- Click Create New Query.
- In the field What do you want to call the new query?, enter a name for your new query.
- Select the base query/table under Which query/table do you want this new query to be based on?.
- Click Create and Edit Source.
- LabKey Server will generate a default SQL query for the selected table.
- Table and column names including spaces must be quoted. For readability you can specify a 'nickname' for the table ("PE" shown above) and use it in the query.
- Click the Data tab to see the results (so far just the original table).
- Return to the Source tab and click Save & Finish to save your query.
Refine the source of this query as desired in the
SQL source editor.
For example, you might calculate the average temperature per participant as shown here:
SELECT PE.ParticipantID,
ROUND(AVG(PE.Temperature), 1) AS AverageTemp
FROM "Physical Exam" PE
GROUP BY PE.ParticipantID
Related Topics
