STAT Function

STAT generates aggregated statistics based on another result set:

STAT([aggregation], index_of_column_to_aggregate, optional_parameter, optional_parameter... , BQL_query)

Allowable aggregations include: MEDIAN, AVG, MIN, MAX, SUM, COUNT, COUNTDISTINCT, and STDDEV.

Optional parameters can be expressions, constants, columns from the main query, or a combination. Using parameters, the lookup query can depend on expressions derived from the main query itself. This technique allows multi-pass calculations.

Reference each parameter expression in the lookup query using %index syntax. The first parameter is numbered %0, the second %1, etc.

Note: Parameters require the lookup query to be issued for each row in the parent query and can affect performance. Use the display filter DISPLAY WHERE syntax to run these queries against the returned dataset in cache for improved performance. See Result Sets and Display Operations.

Note: STAT can return a value from a data set that does not join with the data in the report. For example, the grayed-out attributes or measures in Designer or Visualizer which do not join with what is already on the report can be accessed with STAT.

Note: STAT queries in a pushdown enabled space now return NULL instead of Zero.

Example STAT Function

As an example, show how many products sold for less than their cost:

STAT(COUNT, 0, SELECT [Products.Products], [Order_Date: Sum: Unit_Price] FROM [ALL] WHERE [Order_Date: Sum: Unit_Price] - [Order_Date: Sum: Unit_Cost] <= 0)

To show that in context of Categories and Revenue:

SELECT TOP 100 USING OUTER JOIN STAT(COUNT, 0, SELECT [Products.Products], [Order_Date: Sum: Unit_Price] FROM [ALL] WHERE [Order_Date: Sum: Unit_Price] - [Order_Date: Sum: Unit_Cost] <= 0) 'COL0' , [Order_Date: Sum: Revenue] 'COL1' , [Products.Categories] 'COL2' FROM [ALL]

As expected, the business user would want the answer to be zero.

Table 1. Query Results
Categories	Revenue	STAT COUNT Products Sold for Less Than Cost
Desktops	150,413,715	0
Mobile Phones	101.721.700	0
MP3 Players	27.047,423	0
Notebooks	180,664,684	0
Tablets	204,825,336	0
	Σ 664,672,858	{} 0

Example STAT Function Using a Lookup Parameter

Example highlighting the parameter syntax in a STAT lookup:

SELECT [Products.CategoryName], STAT(Sum, 1,[Order_Date: Sum: Quantity],
          SELECT [Products.CategoryName],[Order_Date: Sum: Quantity] FROM [ALL] DISPLAY WHERE
          [Order_Date: Sum: Quantity]>=%0)/[Order_Date: Sum:
          Quantity]{Products.Total}

Note: Using parameters in this STAT expression supports a typical Pareto chart calculation. The subquery filters for all values which are at or above the value from the main query.