Aggregate SQL Functions

This topic covers all functions built into the Manifold query engine, called SQL functions or query functions, that are aggregate functions.  An aggregate function or clause takes a group, which is a subset of records from the original table, and from the values in that group computes one or more resulting values for the resulting record.   For example, the aggregate Sum(f) takes all values in the group, sums them and returns the sum.   See the Aggregates topic for examples.

 

The Manifold query engine also supports declaring and calling functions, including script functions, functions in external files and compiled functions as .NET assemblies.  

 

 

 

Manifold has so many SQL functions they have been grouped into several topics:

 

 

 

 

 

 

 

 

The list of functions below uses the same nomenclature as the function templates in the Query Builder.  SQL functions take arguments as specified in angle < > brackets.  The data type or value type the function returns is given after the : colon.    For example, Chr(<value>) : <string> takes a numeric <value> and returns a text <string>, as noted in the description a string that is a single character.   CoordSystems() : <table> takes no arguments and returns a table.

 

Examples in the list below which use an ? expression evaluation command are intended to be run in a Command Window.   Do not add a semicolon ; character when using the ? command in a Command window.  When using the ? command we use CALL when the function returns more than just a value, for example, when it returns a table.

 

Indexes are always zero based: for example, in an object consisting of three branches the first branch is branch 0, the second is branch 1 and the third is branch 2.

 

This is a long list, so to find a function it is best when viewing this topic in a browser to do a Ctrl-F to open a Find box and to enter the function name of interest to be able to jump to that function.  The list in this topic is comprehensive but new items are often added with updates to Manifold and may appear in the product before they appear in this documentation.   See the list in the query builder tab of the Command Window for an authoritative list of operators, commands and functions.

Constants

See the Identifiers, Constants and Literals topic for some useful constants.   For information on functions that use a <filter> argument or which produce a filter definition matrix, see the How Matrix Filters Work topic.

Aggregate Functions

For a discussion of aggregate functions, see the Aggregates topic.

 

Avg(<value>) : <value>

An aggregate function:  Returns the average value of the value field.

 

SELECT Avg([Population]) FROM [States Table];

 

Corr(<value>, <value>) : <value>

An aggregate function:  Takes two parameters and computes correlation.  

Count(<value>) : <value>

An aggregate function:  Counts the number of non-NULL values in a field or expression or, as Count(*), the total number of records.  

 

SELECT Count([Population]) FROM [States Table];

 

Covar(<value>, <value>) : <value>

An aggregate function:  Takes two parameters and computes sample covariance.  

CovarPop(<value>, <value>) : <value>

An aggregate function:  Takes two parameters and computes population covariance.  

Diversity(<value>) : <value>

An aggregate function:  Returns the total number of different values in the value field. 

DiversityIndex(<value>) : <value>

An aggregate function:  Returns the diversity index of the value field, a measure of diversity:

 

1 - sum(individualcount^2) / (totalcount^2)

 

A diversity index of 0 means that all values are equal.

 

First(<value>) : <value>

An aggregate function:  Returns the first value for the specified field.  Does not skip NULLs.  Given that tables should be considered unordered, should be used with care, as the first item in an unordered list cannot be considered better than a random choice. Instead, use First with results tables that have been ordered with ORDER BY.

 

SELECT First([Product Name]) FROM [Products];

 

The query above reports a single, random product name.  

 

SELECT First([Product Name])

  FROM (SELECT [Product Name]

          FROM [Products]

          ORDER BY [Product Name]);

 

The above query reports the single product name that comes first in a list sorted by product name.

FirstNonNull(<value>): <value>

An aggregate function: Returns the first value for the specified field which is not a NULL.

GeomMergeAreas(<geom>) : <geom>

An aggregate function:  Takes a set of areas and returns an area geom that contains branches.  Curves and Z values are removed.

GeomMergeLines(<geom>) : <geom>

An aggregate function:  Takes a set of lines and returns a line geom that contains branches.  Curves and Z values are removed.

GeomMergePoints(<geom>) : <geom>

An aggregate function:  Takes a set of points and returns a multipoint geom that contains branches.  Z values are removed.  

GeomUnionAreas(<geom>) : <geom>

An aggregate function: Given a set of geoms that contain areas returns a geom giving their union as a single area.  Discards lines and points.   Any touching or overlapping areas are unioned into a single branch of the area while areas that do not touch or overlap will be separate branches.

GeomUnionRects(<valuex4>) : <valuex4>

An aggregate function: Given a set of x4 values where each x4 value defines diagonally opposite corners of a rectangle, return a single x4 value that gives the union of those rectangles, that is the common bounding box of those rectangles.   

JoinAnd(<boolean>) : <boolean>

An aggregate function: Combine boolean values using the And operator and return a boolean value.

JoinBitAnd(<value>) : <value>

An aggregate function: Combine numeric values using the BitAnd operator and return a boolean value.

JoinBitOr(<value>) : <value>

An aggregate function: Combine numeric values using the BitOr operator and return a boolean value.

JoinBitXor(<value>) : <value>

An aggregate function: Combine numeric values using the BitXor operator and return a boolean value.

JoinOr(<boolean>) : <boolean>

An aggregate function: Combine boolean values using the Or operator and return a boolean value.

JoinXor(<boolean>) : <boolean>

An aggregate function: Combine boolean values using the Xor operator and return a boolean value.

Last(<value>) : <value>

An aggregate function: Returns the last value for the specified field.  Does not skip NULLs. Given that tables should be considered unordered, should be used with care, as the last item in an unordered list cannot be considered better than a random choice. Instead, use Last with results tables that have been ordered with ORDER BY.

 

SELECT Last([Product Name]) FROM [Products];

 

The query above reports a single, random product name.  

 

SELECT Last([Product Name])

  FROM (SELECT [Product Name]

          FROM [Products]

          ORDER BY [Product Name]);

 

The above query reports the single product name that comes last in a list sorted by product name.

LastNonNull(<value>): <value>

An aggregate function: Returns the last value for the specified field which is not a NULL.

Major(<value>) : <value>

An aggregate function:  Returns the the most frequently occurring value in the value field. If all values are different, Major reports the lowest.

 

Max(<value>) : <value>

An aggregate function:  Returns the maximum value for the specified field.  

 

SELECT Max([Population]) FROM [States Table];

 

Median(<value>) : <value>

An aggregate function:  Returns the median value for the specified field.  

 

SELECT Median([Population]) FROM [States Table];

 

Min(<value>) : <value>

An aggregate function:  Returns the minimum value for the specified field.  

 

SELECT Min([Population]) FROM [States Table];

 

StDev(<value>) : <value>

An aggregate function:  Returns the standard deviation of values found in this field in aggregated records, that is, the square root of the sample variance.

 

SELECT StDev([Population])

  FROM [Provinces Table];

 

StDevPop(<value>) : <value>

An aggregate function:  Returns the standard deviation of values found in this field in the entire table, that is, the square root of the population variance.   The method name is a mnemonic for the standard deviation for an entire population.

 

SELECT StDevPop([Population])

  FROM [Provinces Table];

 

StringJoinTokens(<value>, <separator>) : <value>

An aggregate function:  Given string values and a separator string, returns a single string where the values are separated by the separator

 

SELECT StringJoinTokens([First Name], ' -x- ')

  FROM [Employees];

 

Using the Nwind sample database, the above query results in a single record with a result field of:

 

Nancy -x- Andrew -x- Janet -x- Margaret -x- Steven -x- Michael -x- Robert -x- Laura -x- Anne -x- Albert -x- Tim -x- Caroline -x- Justin -x- Xavier -x- Laurent

 

The function can handle personalized separators for each passed value:

 

SELECT StringJoinTokens([First Name], CASE WHEN [First Name] LIKE 'A%' THEN ' -x- ' ELSE ' ==> ' END)

  FROM [Employees];

 

Results in:

 

Nancy -x- Andrew ==> Janet ==> Margaret ==> Steven ==> Michael ==> Robert ==> Laura -x- Anne -x- Albert ==> Tim ==> Caroline ==> Justin ==> Xavier ==> Laurent

 

Passing such personalized separators makes it possible to inject tokens that can be replaced by newline or other characters in output results.

 

Sum(<value>) : <value>

An aggregate function:  Given a value field returns the sum of the values in that field.  

 

SELECT Sum([Population]) FROM [Provinces Table];

 

See an example in the GROUP BY topic.

Var(<value>) : <value>

An aggregate function:  Given a value field returns sample variance.  

 

SELECT Var([Population]) FROM [States Table];

 

VarPop(<value>) : <value>

An aggregate function:  Given a value field returns population variance.  

 

SELECT VarPop([Population]) FROM [States Table];

 

 

Notes

New functions - The list in this topic is intended to be comprehensive but might be not up to date.  New items are often added with updates to Manifold and may appear in the product before they appear in this documentation.   See the list in the query builder tab of the Command Window for an authoritative list of operators, commands and functions.

 

Z values - All functions support Z values.

 

M values - Functions creating geoms from other formats tolerate M values in formats that have them but ignore those M values, discarding them.

 

Curvilinear objects -  Functions for GML, WKB and WKT support curvilinear objects for all types supported by each format:  circular arcs, ellipsoidal arcs, splines for GML and circular arcs for WKB and WKT.

 

Division by zero - returns NULL.

 

All types of values - Operators and functions generally  support all types of values so that, for example, we can use comparison or boolean operators on tiles and not just on scalar values.  

 

Everything Math - For a handy reference to anything in mathematics, see the Wolfram MathWorld site.   Thank you, Wolfram!

 

See Also

Tables

 

Add an Index to a Table

 

Functions

 

Queries

 

Regular Expressions

 

How Matrix Filters Work

 

Command Window

 

Command Window - Query Builder

 

Identifiers, Constants and Literals

 

SQL Statements

 

SQL Operators

 

SQL Functions

 

Coord SQL Functions

 

Geom SQL Functions

 

String SQL Functions

 

Tile SQL Functions

 

Other SQL Functions

 

Temporary Databases

 

EXECUTE

 

Example: Create and Run a Query -  See how the different parts of a command window operate when creating and running SQL queries.   Includes use of the Log tab as well as the ?expression and !fullfetch commands.

 

Example: Transfer DEM Terrain Heights to Areas in a Drawing - Given a map with an image layer that shows terrain heights taken from a DEM, and a drawing layer that contains areas, using a small SQL query we transfer the average terrain height within each area to that area as a Height attribute for the area. Easy!

 

SQL Example: Process Images with 3x3 Filters -  Shows a step-by-step example of developing an SQL query that takes a query written by the Edit Query button and then modifies that query into a general purpose query that can apply any 3x3 filter.   This makes it easy to use matrix filters we find on the web for custom image processing.   We extend the query by using parameters and adding a function, and then show how it can be adapted to use a 5x5 filter.

 

SQL Example: Process Images using Dual 3x3 Filters  - A continuation of the above topic, extending the example query to utilize two filters for processing, as commonly done with Sobel and Prewitt two filter processing.

 

SQL Example: Process RGB Images using Matrix Filters - A continuation of the above two topics, extending the example query to process three channel, RGB images.

 

SQL Example: Miscellaneous SQL Functions - A variety of small examples using SQL functions.

 

SQL Example: GeomOverlayAdjacent Function - Using the GeomOverlayAdjacent function, an example that shows how this function and similar functions such as GeomOverlayContained, GeomOverlayContaining, GeomOverlayIntersecting and GeomOverlayTouching operate.

 

SQL Example: GeomOverlayTopologyUnion Function - A continuation of the SQL Example: GeomOverlayAdjacent Function example, using the GeomOverlayTopologyUnion function, an example that shows how this function and similar functions such as GeomOverlayTopologyIdentity, GeomOverlayTopologyIntersect and GeomOverlayTopologyUpdate operate.

 

Example: Merge : areas (dissolve) - In this example we combine multiple area objects into a single area object by using the Merge template in the Transform pane, using the areas (dissolve) option.  A drawing of French regions shows some regions as more than one area.  We would like each region to be one area so the table of regions has one record per region.

 

SQL Example: Learn SQL from Edit Query - Merging Areas - We learn how to write an SQL query that does a Merge : area (dissolve) operation by cutting and pasting from what the Edit Query button automatically generates.

 

Example: Use a Transform Expression to Create Buffers in a Drawing - Use the Expression tab of the Transform pane to create three different sizes of buffers for different lines in a drawing and then automatically create a query which does the same thing.  Includes examples of using the Add Component button and also the Edit Query button.

 

Example: Clip Areas with a Transform Expression - Use the Expression tab of the Transform pane to clip areas in a drawing to fit within horizontal bounds.   Includes examples of using the Add Component button and also the Edit Query button.

 

Example: Transform Templates, Expressions and Queries - We learn to use a function by clicking on a template in the Transform pane, seeing what it does in a preview, looking at the query Manifold creates and then trying out the function in the Expression tab.

 

SQL Example: List Transformation Grids - In this example we use a few snippets of easy SQL to list NTv2 and NADCON transformation grids that are available within the grids.dat compressed collection of transformation grid files.   Grid transformation files can be used when creating custom base coordinate systems, for NADCON / HARN / HPGN and NTv2 high accuracy transformations that use grid files.