This topic covers information helpful in writing some flow-specific queries. Users who are new to custom queries should start with this section of the documentation:
LabKey SQL provides the "Statistic" method on FCS tables to allow calculation of certain statistics for FCS data.
Example: StatisticDemo Query
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 colums:
- Add a line to calculate the 'Count' statistic like this; remember to add the comma to the prior line.
- 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:
Example: SubsetDemo Query
It is possible to calculate a suite of statistics for every well in an FCS file using an INNER JOIN technique in conjunction with the "Statistic" method. This technique can be complex, so we present an example to provide an introduction to what is possible.
Create a Query
For this example, we use the FCSAnalyses table
in the Peptide Validation Demo
, a more complex demo than the one used in the Flow Tutorial
. We create a query
called "SubsetDemo" using the "FCSAnalyses" table in the "flow" schema and edit it in the SQL Source Editor.
FCSAnalyses.FCSFile.Run AS ASSAYID,
FCSAnalyses.FCSFile.Sample AS Sample,
FCSAnalyses.FCSFile.Keyword."WELL ID" AS WELL_ID,
FCSAnalyses.Statistic."Count" AS COLLECTCT,
FCSAnalyses.Statistic."S:Count" AS SINGLETCT,
FCSAnalyses.Statistic."S/Lv:Count" AS LIVECT,
FCSAnalyses.Statistic."S/Lv/L:Count" AS LYMPHCT,
FCSAnalyses.Statistic."S/Lv/L/3+:Count" AS CD3CT,
FCSAnalyses.Statistic(Subsets.STAT_TCELLSUB) AS NSUB,
FCSAnalyses.FCSFile.Keyword.Stim AS ANTIGEN,
FCSAnalyses.Statistic(Subsets.STAT_CYTNUM) AS CYTNUM,
INNER JOIN lists.ICS3Cytokine AS Subsets ON Subsets.PFD IS NOT NULL
WHERE FCSAnalyses.FCSFile.Keyword."Sample Order" NOT IN ('PBS','Comp')
Examine the Query
This SQL code leverages the FCSAnalyses table and a list of desired statistics to calculate those statistics for every well.
The "Subsets" table in this query comes from a user-created list called "ICS3Cytokine"
in the Flow Demo. It contains the group of statistics we wish to calculate for every well.
Results are available in this table