avgif

avgif — aggregate function to return the average of rows that match the query's predicate.

SYNOPSIS

avgif(T column, bool predicate)

DESCRIPTION

The avgif() function returns the average of the values for which the specified numeric column is not null and for which the predicate evaluates to true. The predicate expression can be any valid expression on any column in a table, is not restricted to values from the specified column.

EXAMPLE

This example shows how to use dynamic aggregation using this function. In the Hello LiveView sample, create a query that finds the average large sale value per item type for non-null transactions in the ItemSales table. Follow these steps:

  1. In LiveView Desktop connected to a server running the Hello LiveView sample, select the ItemsSales table from the Tables pane of the LiveView Tables view.

  2. In the Select field, enter: 

     Item, avgif (transactionID, lastSoldPrice > 75) as AverageBigSales

  3. In the Query field, enter the following: 

    group by Item

  4. Click Open Query.

The query results open in a grid view. LiveView Server recalculates the AverageBigSales values whenever the rows returned by the query predicate change.

SEE ALSO

This LiveView aggregate function is based on the avgif aggregate function in the StreamBase expression language.