LabKey SQL provides the "Statistic" method on FCS tables to allow calculation of certain statistics for FCS data.
To use this method, you can either:
- Use the SQL Designer to add/remove "Statistic" fields to a FCS query.
- Use the SQL Editor to call the "Statistic" method on the FCS table of interest.
Example
For this example, we
create a query called "StatisticDemo" based on the
FCSAnalyses dataset. (You can see a complete version of this query here:
StatisticDemo.)
Create a New Query
- Select (Admin) > Developer Links > Schema Browser.
- Click flow to open the flow schema.
- Click Create New Query.
- Call your new query "StatisticDemo"
- Select FCSAnalyses as the base for your new query.
- Click Create and Edit Source.
Add Statistics to the Generated SQL
The default SQL simply selects all the columns:
SELECT FCSAnalyses.Name,
FCSAnalyses.Flag,
FCSAnalyses.Run,
FCSAnalyses.CompensationMatrix
FROM FCSAnalyses
- Add a line to calculate the 'Count' statistic like this; remember to add the comma to the prior line.
SELECT FCSAnalyses.Name,
FCSAnalyses.Flag,
FCSAnalyses.Run,
FCSAnalyses.CompensationMatrix,
FCSAnalyses.Statistic."Count"
FROM FCSAnalyses
- Click Save.
- Click the Data tab. The "Count" statistic has been calculated using the Statistic method on the FCSAnalyses table, and is shown on the right.
You can flip back and forth between the source, data, and xml metadata for this query using the tabs in the query editor.
Run the Query
From the "Source" tab, to see the generated query, either view the "Data" tab, or click the
Execute Query button. To leave the query editor, click
Save & Finish.
The
resulting table includes the "Count" column on the right:
View this query applied to a more complex dataset. The dataset used in the Flow Demo has been slimmed down for ease of use. A larger, more complex dataset produces a more interesting "Count" column, as seen in
this table and the screenshot below:
