SQL aggregate functions and NULL handling
Count rows, total values, and summarize groups with Haybarn SQL. Understand how NULL values and empty inputs affect aggregate results.
Choose a function
Start by defining what one output row represents, then choose the GROUP BY keys. Use FILTER on individual aggregates when different metrics need different subsets of the same input.
count()Count rows or non-null values.sum()Total numeric values.avg()Calculate the mean of non-null values.median()Find the middle value of a distribution.
Joins can multiply rows before aggregation and inflate totals. Check the input row count first. count(*) counts rows, while count(column) excludes NULL values in that column.
Count rows and non-null values
Count all rows with count(*) or count only non-null values with count(expression). Add DISTINCT to count distinct non-null values.
Unlike most aggregates, count returns zero on an empty input. NULL values are excluded from count(expression).
Guide example Β· Run to view results.
| rows | non_null | distinct_values |
|---|---|---|
3 | 2 | 1 |
Watch for: After a LEFT JOIN, count(*) includes the preserved unmatched row. Count a non-null key from the joined table to count matches.
All aggregates functions
78 functions in Haybarn WASM 2.0.0-rc12. Browse the filtered catalog β
any_value()Returns the first non-NULL value from arg. This function is affected by ordering.
approx_count_distinct()Computes the approximate count of distinct elements using HyperLogLog.
approx_quantile()Computes the approximate quantile using T-Digest.
approx_top_k()Finds the k approximately most occurring values in the data set
arbitrary()Returns the first value (NULL or non-NULL) from arg. This function is affected by ordering.
arg_max()Finds the row with the maximum val. Calculates the non-NULL arg expression at that row.
arg_max_null()Finds the row with the maximum val. Calculates the arg expression at that row.
arg_max_nulls_last()Finds the rows with N maximum vals, including nulls. Calculates the arg expression at that row.
arg_min()Finds the row with the minimum val. Calculates the non-NULL arg expression at that row.
arg_min_null()Finds the row with the minimum val. Calculates the arg expression at that row.
arg_min_nulls_last()Finds the rows with N minimum vals, including nulls. Calculates the arg expression at that row.
argmax()Finds the row with the maximum val. Calculates the non-NULL arg expression at that row.
argmin()Finds the row with the minimum val. Calculates the non-NULL arg expression at that row.
avg()Calculates the average value for all tuples in x.
bit_and()Returns the bitwise AND of all bits in a given expression.
bit_or()Returns the bitwise OR of all bits in a given expression.
bit_xor()Returns the bitwise XOR of all bits in a given expression.
bitstring_agg()Returns a bitstring with bits set for each distinct value.
bool_and()Returns TRUE if every input value is TRUE, otherwise FALSE.
bool_or()Returns TRUE if any input value is TRUE, otherwise FALSE.
combine_aggr()Combines exported aggregate states
corr()Returns the correlation coefficient for non-NULL pairs in a group.
count()Count all rows with count(*) or count only non-null values with count(expression). Add DISTINCT to count distinct non-null values.
count_if()Counts the total number of TRUE values for a boolean column
count_star()countif()Counts the total number of TRUE values for a boolean column
covar_pop()Returns the population covariance of input values.
covar_samp()Returns the sample covariance for non-NULL pairs in a group.
decimal_average()Computes the average of a DECIMAL column using exact integer arithmetic with banker's rounding. Returns a DECIMAL with the same width and scale as the input.
entropy()Returns the log-2 entropy of count input-values.
favg()Calculates the average using a more accurate floating point summation (Kahan Sum)
first()Returns the first value (NULL or non-NULL) from arg. This function is affected by ordering.
fsum()Calculates the sum using a more accurate floating point summation (Kahan Sum).
group_concat()Concatenates the column string values with an optional separator.
histogram()Returns a LIST of STRUCTs with the fields bucket and count.
histogram_exact()Returns a LIST of STRUCTs with the fields bucket and count matching the buckets exactly.
kahan_sum()Calculates the sum using a more accurate floating point summation (Kahan Sum).
kurtosis()Returns the excess kurtosis (Fisherβs definition) of all input values, with a bias correction according to the sample size
kurtosis_pop()Returns the excess kurtosis (Fisherβs definition) of all input values, without bias correction
last()Returns the last value of a column. This function is affected by ordering.
list()Returns a LIST containing all the values of a column.
listagg()Concatenates the column string values with an optional separator.
lttb()Downsamples the (x, y) points to (at most) n representative points using the Largest Triangle Three Buckets algorithm. Returns a LIST of STRUCT(x, y); the points must be ordered by x (use lttb(x, y, n ORDER BY x)).
mad()Returns the median absolute deviation for the values within x. NULL values are ignored. Temporal types return a positive INTERVAL.
max()Returns the maximum value present in arg.
max_by()Finds the row with the maximum val. Calculates the non-NULL arg expression at that row.
mean()Calculates the average value for all tuples in x.
median()Returns the middle value of the set. NULL values are ignored. For even value counts, interpolate-able types (numeric, date/time) return the average of the two middle values. Non-interpolate-able types (everything else) return the lower of the two middle values.
min()Returns the minimum value present in arg.
min_by()Finds the row with the minimum val. Calculates the non-NULL arg expression at that row.
mode()Returns the most frequent value for the values within x. NULL values are ignored.
product()Calculates the product of all tuples in arg.
quantile()Returns the exact quantile number between 0 and 1 . If pos is a LIST of FLOATs, then the result is a LIST of the corresponding exact quantiles.
quantile_cont()Returns the interpolated quantile number between 0 and 1 . If pos is a LIST of FLOATs, then the result is a LIST of the corresponding interpolated quantiles.
quantile_disc()Returns the exact quantile number between 0 and 1 . If pos is a LIST of FLOATs, then the result is a LIST of the corresponding exact quantiles.
regr_avgx()Returns the average of the independent variable for non-NULL pairs in a group, where x is the independent variable and y is the dependent variable.
regr_avgy()Returns the average of the dependent variable for non-NULL pairs in a group, where x is the independent variable and y is the dependent variable.
regr_count()Returns the number of non-NULL number pairs in a group.
regr_intercept()Returns the intercept of the univariate linear regression line for non-NULL pairs in a group.
regr_r2()Returns the coefficient of determination for non-NULL pairs in a group.
regr_slope()Returns the slope of the linear regression line for non-NULL pairs in a group.
regr_sxx()regr_sxy()Returns the population covariance of input values
regr_syy()reservoir_quantile()Gives the approximate quantile using reservoir sampling, the sample size is optional and uses 8192 as a default size.
sem()Returns the standard error of the mean
skewness()Returns the skewness of all input values.
stddev()Returns the sample standard deviation
stddev_pop()Returns the population standard deviation.
stddev_samp()Returns the sample standard deviation
string_agg()Concatenates the column string values with an optional separator.
sum()Add numeric values within a group, ignoring NULL inputs. Use GROUP BY to produce a separate total for each group.
sum_no_overflow()Internal only. Calculates the sum value for all tuples in arg without overflow checks.
sumkahan()Calculates the sum using a more accurate floating point summation (Kahan Sum).
var_pop()Returns the population variance.
var_samp()Returns the sample variance of all input values.
variance()Returns the sample variance of all input values.
variant_group_object()Returns a VARIANT object containing all key, value pairs in the aggregation.