Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value. This makes it possible to generate summary statistics for multi-level groups with little overhead. Aggregate functions in Windows Search SQL have the following syntax:

AGGREGATE <function> [as <label>] [,<function> [as <label>]]*

The function portion includes the following functions and syntax:

Function	Description
MAX(<column>)	Returns the maximum value in the expression
MIN(<column>)	Returns the minimum value in the expression
AVG(<column>)	Returns the average of the values in a group
SUM(<column>)	Returns the sum of the values in a group
COUNT()	Returns the number of items in a group and all subgroups
CHILDCOUNT()	Returns the number of items in a group (without subgroups)

You can use any numeric or date column for aggregations; you aren't restricted to only those in the SELECT clause. However, you cannot group on aggregates.

Note  A syntax error is returned if the column argument passed in is of a non-numeric or non-date type.

The label portion is optional and provides a more readable alias for the label. If you do not include an alias label, Windows Search transforms the function and column name into a label. For example, MAX(System.Document.WordCount) becomes MAX_SystemDocumentWordCount.

Examples

The following are examples of aggregate functions within the GROUP ON clause:

GROUP ON System.Sender ['d','m','r'] 
    AGGREGATE COUNT() as 'Total',
              MAX(System.Size) as 'Max Size',
              MIN(System.Size) as 'Min Size'
              
GROUP ON System.Sender ['d', 'm', 'r']
      AGGREGATE MAX(System.Search.Rank) as 'MaxRank', 
                MIN(System.Search.Rank) as 'MinRank'
      OVER (GROUP ON system.conversationID
                  OVER (SELECT workid, path FROM systemindex
                        WHERE CONTAINS (*, 'sometext')
                        ORDER BY System.DateCreated))
               
GROUP ON System.FileName [before('a'),before('z')] 
      AGGREGATE MAX(System.Size) as 'maxsize', MIN(System.Size) as 'MinSize' 
      OVER (SELECT System.FileName FROM systemindex
            ORDER BY System.FileName)      
            
GROUP ON System.author 
      AGGREGATE MAX(System.size) as 'maxsize' 
      ORDER BY min(System.Size) 
      OVER (GROUP ON System.DateCreated 
                  AGGREGATE Count() 
                  ORDER BY MAX(System.size) 
                  OVER (SELECT top 25 filename, System.DateCreated, System.Size FROM systemindex
                        WHERE CONTAINS('text')))

The return value is a variant found on the rowset as a custom property, either as the specified aliases or as "Aggregates" if no alias label is specified.