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:

• SQL Queries

• Example: StatisticDemo Query - Use the SQL Designer to add/remove "Statistic" fields to a FCS query.
• Example: SubsetDemo Query - Calculate suites of statistics for every well.

Example: StatisticDemo Query

For this example, we create a query called "StatisticDemo" based on the FCSAnalyses dataset. Start from your Flow demo folder, such as that created during the Flow Tutorial.

Create a New Query

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

SELECT FCSAnalyses.Name,
  FCSAnalyses.Flag,
  FCSAnalyses.Run,
  FCSAnalyses.CompensationMatrix,
  FCSAnalyses.Statistic."Count"
FROM FCSAnalyses

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 Execute Query. 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 Tutorial has been slimmed down for ease of use. A larger, more complex dataset can be seen in this table:

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. We create a query called "SubsetDemo" using the "FCSAnalyses" table in the "flow" schema and edit it in the SQL Source Editor.

SELECT 
  FCSAnalyses.FCSFile.Run AS ASSAYID,
  FCSAnalyses.FCSFile.Sample AS Sample,
  FCSAnalyses.FCSFile.Sample.Property.PTID,
  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,
  Subsets.TCELLSUB,
  FCSAnalyses.Statistic(Subsets.STAT_TCELLSUB) AS NSUB,
  FCSAnalyses.FCSFile.Keyword.Stim AS ANTIGEN,
  Subsets.CYTOKINE,
  FCSAnalyses.Statistic(Subsets.STAT_CYTNUM) AS CYTNUM,
FROM FCSAnalyses
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.

View Results

Results are available in this table.

Related Topics

• Flow Module Schema