Guinsoo
GitHub
  • Welcome to Guinsoo!
  • Guides
    • Introduction
    • Quickstart
    • Features
    • Installation
    • Tutorial
      • Using the Server
      • Clients for Guinsoo
        • NodeJS
        • Go
        • Rust
        • C++
        • Python
        • Java
      • Web UI for Guinsoo
      • The Shell Tool
      • CSV Support
    • Security
    • Performance
    • Advanced
  • Reference
    • Commands
    • Functions
    • Aggregate
    • Window
    • Data Types
    • SQL Grammar
    • System Table
  • Support
    • FAQ
  • Appendix
    • Links
    • Architecture
    • Contribute
    • License
Powered by GitBook
On this page
  • Index
  • Details
  • General Aggregate Functions
  • Binary Set Functions
  • Ordered Aggregate Functions
  • Hypothetical Set Functions
  • Inverse Distribution Functions
  • JSON Aggregate Functions

Was this helpful?

  1. Reference

Aggregate

SQL Aggregate Functions

PreviousFunctionsNextWindow

Last updated 2 years ago

Was this helpful?

Index

Details

General Aggregate Functions

AVG

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

Return average (mean) value. If no rows are selected, the result is NULL. Aggregation are only allowed in select statements.

The data type of result is DOUBLE PRECISION for TINYINT, SMALLINT, INTEGER, and REAL arguments, NUMERIC with additional 10 decimal digits of precision and scale for BIGINT and NUMERIC arguments; DECFLOAT with additional 10 decimal digits of precision for DOUBLE PRECISION and DECFLOAT arguments; INTERVAL with the same leading field precision, all additional smaller datetime units in interval qualifier, and the maximum scale for INTERVAL arguments.

Examples:

AVG(X)

MAX

MAX(value)
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

The highest value. If no rows are selected, the result is NULL. Aggregate are only allowed in

select statements. The returned value is of the same data type are the parameter.

Examples:

MAX(X)

MIN

MIN(value)
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

The lowest value. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements. The returned value is of the same data type as the parameter.

Examples:

MIN(X)

SUM

SUM( [ DISTINCT|ALL ] { numeric | interval | { boolean } } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

The sum of all values. If no rows are selected, the result is NULL.Aggregates are only allowed in select statements.

Examples:

SUM(X)

EVERY

{EVERY| {BOOL_AND}}(boolean)
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

Returns true if all expressions are true. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements.

Examples:

EVERY(ID>10)

ANY

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

Returns true if any expression is true. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements.

Note that if ANY or SOME aggregate function is placed on the right side of comparison operation or distinct predicate and argument of this function is a subquery additional parentheses around aggregate function are required, otherwise it will be parsed as quantified predicate.

Examples:

ANY(NAME LIKE 'W%')
A = (ANY((SELECT B FROM C)))

COUNT

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

STDDEV_POP

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

STDDEV_SAMP

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

VAR_POP

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

VAR_SAMP

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

BIT_AND_AGG

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

BIT_OR_AGG

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

BIT_XOR_AGG

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

BIT_NAND_AGG

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

BIT_NOR_AGG

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

BIT_XNOR_AGG

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

ENVELOPE

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

Binary Set Functions

COVAR_POP

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

COVAR_SAMP

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

CORR

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

REGR_SLOPE

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

REGR_INTERCEPT

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

REGR_COUNT

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

REGR_R2

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

REGR_AVGX

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

REGR_AVGY

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

REGR_SXX

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

REGR_SYY

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

REGR_SXY

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

Ordered Aggregate Functions

LISTAGG

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

ARRAY_AGG

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

Hypothetical Set Functions

RANK aggregate

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

DENSE_RANK aggregate

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

PERCENT_RANK aggregate

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

CUME_DIST aggregate

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

Inverse Distribution Functions

PERCENTILE_CONT

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

PERCENTILE_DISC

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

MEDIAN

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

MODE

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

JSON Aggregate Functions

JSON_OBJECAGG

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

JSON_ARRAYAGG

AVG ( [ DISTINCT|ALL ] { numeric | interval } )
[FILTER (WHERE expression)] [OVER windowNameOrSpecification]

TODO

General Aggregate Function
AVG
MAX
MIN
SUM
EVERY
ANY
COUNT
STDDEV_POP
STDDEV_SAMP
VAR_POP
VAR_SAMP
BIT_AND_AGG
BIT_OR_AGG
BIT_XOR_AGG
BIT_NAND_AGG
BIT_NOR_AGG
BIT_XNOR_AGG
ENVELOPE
Binary Set Functions
COVAR_POP
COVAR_SAMP
CORR
REGR_SLOPE
REGR_INTERCEPT
REGR_COUNT
REGR_R2
REGR_AVGX
REGR_AVGY
REGR_SXX
REGR_SYY
REGR_SXY
Ordered Aggregate Functions
LISTAGG
ARRAY_AGG
Hypothetical Set Functions
RANK aggregate
DENSE_RANK aggregate
PERCENT_RANK aggregate
CUME_DIST aggregate
Inverse Distribution Functions
PERCENTILE_COUNT
PERCENTILE_DISC
MEDIAN
MODE
JSON Aggregate Functions
JSON_OBJECTAGG
JSON_ARRAYAGG