github.com/mithrandie/csvq@v1.18.1/docs/_posts/2006-01-02-aggregate-functions.md (about)

     1  ---
     2  layout: default
     3  title: Aggregate Functions - Reference Manual - csvq
     4  category: reference
     5  ---
     6  
     7  # Aggregate Functions
     8  
     9  Aggregate functions calculate groupd records retrieved by a select query.
    10  If records are not grouped, all records are dealt with as one group.
    11  
    12  If distinct option is specified, aggregate functions calculate only unique values.
    13  
    14  Aggregate Functions can be used only in [Select Clause]({{ '/reference/select-query.html#select_clause' | relative_url }}), [Having Clause]({{ '/reference/select-query.html#having_clause' | relative_url }}) and [Order By Clause]({{ '/reference/select-query.html#order_by_clause' | relative_url }})
    15  
    16  
    17  | name | description |
    18  | :- | :- |
    19  | [COUNT](#count)       | Return the number of values |
    20  | [MIN](#min)           | Return the minimum value |
    21  | [MAX](#max)           | Return the maximum value |
    22  | [SUM](#sum)           | Return the sum of values |
    23  | [AVG](#avg)           | Return the average of values |
    24  | [STDEV](#stdev)       | Return the sample standard deviation of values |
    25  | [STDEVP](#stdevp)     | Return the population standard deviation of values |
    26  | [VAR](#var)           | Return the sample variance of values |
    27  | [VARP](#varp)         | Return the population variance of values |
    28  | [MEDIAN](#median)     | Return the median of values |
    29  | [LISTAGG](#listagg)   | Return the concatenated string of values |
    30  | [JSON_AGG](#json_agg) | Return the string formatted in JSON array |
    31  
    32  ## Definitions
    33  
    34  ### COUNT
    35  {: #count}
    36  
    37  ```
    38  COUNT([DISTINCT] expr)
    39  ```
    40  
    41  _expr_
    42  : [value]({{ '/reference/value.html' | relative_url }})
    43  
    44  _return_
    45  : [integer]({{ '/reference/value.html#integer' | relative_url }})
    46  
    47  Returns the number of non-null values of _expr_.
    48  
    49  ```
    50  COUNT([DISTINCT] *)
    51  ```
    52  
    53  _return_
    54  : [integer]({{ '/reference/value.html#integer' | relative_url }})
    55  
    56  Returns the number of all values including null values.
    57  
    58  ### MIN
    59  {: #min}
    60  
    61  ```
    62  MIN(expr)
    63  ```
    64  
    65  _expr_
    66  : [value]({{ '/reference/value.html' | relative_url }})
    67  
    68  _return_
    69  : [primitive type]({{ '/reference/value.html#primitive_types' | relative_url }})
    70  
    71  Returns the minimum value of non-null values of _expr_.
    72  If all values are null, then returns null.
    73  
    74  ### MAX
    75  {: #max}
    76  
    77  ```
    78  MAX(expr)
    79  ```
    80  
    81  _expr_
    82  : [value]({{ '/reference/value.html' | relative_url }})
    83  
    84  _return_
    85  : [primitive type]({{ '/reference/value.html#primitive_types' | relative_url }})
    86  
    87  Returns the maximum value of non-null values of _expr_.
    88  If all values are null, then return a null.
    89  
    90  ### SUM
    91  {: #sum}
    92  
    93  ```
    94  SUM([DISTINCT] expr)
    95  ```
    96  
    97  _expr_
    98  : [value]({{ '/reference/value.html' | relative_url }})
    99  
   100  _return_
   101  : [float]({{ '/reference/value.html#float' | relative_url }})
   102  
   103  Returns the sum of float values of _expr_.
   104  If all values are null, then returns a null.
   105  
   106  ### AVG
   107  {: #avg}
   108  
   109  ```
   110  AVG([DISTINCT] expr)
   111  ```
   112  
   113  _expr_
   114  : [value]({{ '/reference/value.html' | relative_url }})
   115  
   116  _return_
   117  : [float]({{ '/reference/value.html#float' | relative_url }})
   118  
   119  Returns the average of float values of _expr_.
   120  If all values are null, then returns a null.
   121  
   122  ### STDEV
   123  {: #stdev}
   124  
   125  ```
   126  STDEV([DISTINCT] expr)
   127  ```
   128  
   129  _expr_
   130  : [value]({{ '/reference/value.html' | relative_url }})
   131  
   132  _return_
   133  : [float]({{ '/reference/value.html#float' | relative_url }})
   134  
   135  Returns the sample standard deviation of float values of _expr_.
   136  If all values are null, then returns a null.
   137  
   138  ### STDEVP
   139  {: #stdevp}
   140  
   141  ```
   142  STDEVP([DISTINCT] expr)
   143  ```
   144  
   145  _expr_
   146  : [value]({{ '/reference/value.html' | relative_url }})
   147  
   148  _return_
   149  : [float]({{ '/reference/value.html#float' | relative_url }})
   150  
   151  Returns the population standard deviation of float values of _expr_.
   152  If all values are null, then returns a null.
   153  
   154  ### VAR
   155  {: #var}
   156  
   157  ```
   158  VAR([DISTINCT] expr)
   159  ```
   160  
   161  _expr_
   162  : [value]({{ '/reference/value.html' | relative_url }})
   163  
   164  _return_
   165  : [float]({{ '/reference/value.html#float' | relative_url }})
   166  
   167  Returns the sample variance of float values of _expr_.
   168  If all values are null, then returns a null.
   169  
   170  
   171  ### VARP
   172  {: #varp}
   173  
   174  ```
   175  VARP([DISTINCT] expr)
   176  ```
   177  
   178  _expr_
   179  : [value]({{ '/reference/value.html' | relative_url }})
   180  
   181  _return_
   182  : [float]({{ '/reference/value.html#float' | relative_url }})
   183  
   184  Returns the population variance of float values of _expr_.
   185  If all values are null, then returns a null.
   186  
   187  
   188  ### MEDIAN
   189  {: #median}
   190  
   191  ```
   192  MEDIAN([DISTINCT] expr)
   193  ```
   194  
   195  _expr_
   196  : [value]({{ '/reference/value.html' | relative_url }})
   197  
   198  _return_
   199  : [float]({{ '/reference/value.html#float' | relative_url }})
   200  
   201  Returns the median of float or datetime values of _expr_.
   202  If all values are null, then returns a null.
   203  
   204  Even if _expr_ represents datetime values, this function returns a float or integer value.
   205  The return value can be converted to a datetime value by using the [DATETIME function]({{ '/reference/cast-functions.html#datetime' | relative_url }}).
   206  
   207  ### LISTAGG
   208  {: #listagg}
   209  
   210  ```
   211  LISTAGG([DISTINCT] expr [, separator]) [WITHIN GROUP (order_by_clause)]
   212  ```
   213  
   214  _expr_
   215  : [value]({{ '/reference/value.html' | relative_url }})
   216  
   217  _separator_
   218  : [string]({{ '/reference/value.html#string' | relative_url }})
   219  
   220  _order_by_clause_
   221  : [Order By Clause]({{ '/reference/select-query.html#order_by_clause' | relative_url }})
   222  
   223  _return_
   224  : [string]({{ '/reference/value.html#string' | relative_url }})
   225  
   226  Returns the string result with the concatenated non-null values of _expr_.
   227  If all values are null, then returns a null.
   228  
   229  _separator_ is placed between values. Empty string is the default.
   230  By using _order_by_clause_, you can sort values.
   231  
   232  ### JSON_AGG
   233  {: #json_agg}
   234  
   235  ```
   236  JSON_AGG([DISTINCT] expr) [WITHIN GROUP (order_by_clause)]
   237  ```
   238  
   239  _expr_
   240  : [value]({{ '/reference/value.html' | relative_url }})
   241  
   242  _order_by_clause_
   243  : [Order By Clause]({{ '/reference/select-query.html#order_by_clause' | relative_url }})
   244  
   245  _return_
   246  : [string]({{ '/reference/value.html#string' | relative_url }})
   247  
   248  Returns the string formatted in JSON array of _expr_.