What is aggregate function in Java

filter - selective aggregates

The clause extends aggregate functions (,,, ...) by an additional clause. The result of the aggregate function is only formed from the rows that also meet this additional clause.

syntax

The clause is followed by aggregate functions:

With the exception of subqueries and window functions, everything is allowed in the clause that is also allowed in a normal clause.

The clause can be applied to all aggregate functions: in addition to the well-known such as and also to and sorted set functions such as.

If an aggregate function is used as a window function (clause), the syntactic order is: aggregate function, clause, clause:

However, the clause is not generally allowed before: is only allowed after aggregate functions, but not after other window functions such as rank functions (,, ...).

use cases

The following articles describe common uses of:

compatibility

The clause was introduced with SQL: 2003 as part of the optional feature "Advanced OLAP operations" (T612). It is hardly supported today, but can easily be emulated with an expression (see Compliant Alternatives).

Compliant alternatives

The -clause can generally be implemented by an -expression in the aggregate function. To do this, the filter condition is transferred to the -clause and the value to be aggregated is transferred to the -clause. Since aggregate functions generally do not take into account1, the implicit clause is sufficient to ignore lines that do not match. The following expressions are therefore equivalent.

Only a special case needs to be mentioned, since you cannot write “*” directly in the clause. However, it is sufficient to replace the star with a non-constant. This ensures that every matching line is counted. The implicit branch supplies the lines that do not match, which is also ignored by.

If a set quantifier (or) is used, it must be retained in the aggregate function.

Proprietary extensions

Subqueries Allowed (PostgreSQL)

The PostgreSQL database supports the use of subqueries in the clause (e.g. by means of).

Proprietary alternatives

Since the standard alternative with an expression described above is largely supported, I generally advise against using these proprietary alternatives.

(Google BigQuery)

Google BigQuery offers the aggregate function, which is best known from Microsoft Excel.

The same result can be achieved with the alternative, which then works in all common databases:

About the author

Markus Winand is the SQL Renaissance Ambassador on a mission to alert developers to the evolution of SQL in the 21st century. Markus can be hired as a trainer, speaker and consultant on winand.at.

Footnotes