The following table describes the aggregate functions supported by Devart ODBC Driver for SQLite. Two more aggregates are described as part of JSON functions and operators.
| Function | Description |
|---|---|
avg(X) |
Returns the average value of all non-NULL values of X within a group; NULL if there are no non-NULL values. The result is always a floating-point value, even if all inputs are integers. String and BLOB values that don’t look like numbers are interpreted as 0. |
count(*) |
Counts the total number of rows in the group. |
count(X) |
Counts the number of non-NULL values of X in the group. |
group_concat(X) |
Concatenates all non-NULL values of X into a single string, separated by commas. |
group_concat(X,Y) |
Concatenates all non-NULL values of X into a single string, separated by Y. |
max(X) |
Returns the maximum value of X in the group; NULL if no non-NULL values exist. |
median(X) |
Returns the median value of all non-NULL values of X in the group. This function is equivalent to percentile_cont(X,0.5). |
min(X) |
Returns the minimum non-NULL value of X in the group; NULL if no non-NULL values exist. |
percentile(Y,P) |
Returns the value that is greater than or equal to P percent of the non-NULL values in Y, and less than or equal to 100-P percent of the values. P must be a number between 0.0 and 100.0, and must be the same for all terms of the aggregate. Y must be NULL (which is ignored) or numeric. |
percentile_cont(Y,P) |
Returns the value that is greater than or equal to fraction P of the non-NULL values in Y, and less than or equal to fraction 1.0-P of the values. P must be a number between 0.0 and 1.0, and must be the same for all terms of the aggregate. Y must be NULL (which is ignored) or numeric. |
percentile_disc(Y,P) |
Works like percentile_cont(Y,P), except that instead of calculating a weighted average of the closest available values in Y it always returns the smaller of the two closest values to P. |
string_agg(X,Y) |
Alias for group_concat(X,Y). |
sum(X) |
Returns the sum of all non-NULL values of X in the group; NULL if no non-NULL values exist. The result is an integer if all non-NULL inputs are integers. If any input is neither an integer nor NULL, the result is a floating-point value that is an approximation of the mathematical sum. |
total(X) |
Returns the sum of all non-NULL values of X in the group; 0.0 if no non-NULL values exist. The result is always a floating-point value. |
For more information, see the SQLite documentation: Built-in Aggregate Functions.