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).

Count rows and non-null values
SQL
Haybarn WASM 2.0.0-rc12 Β· In your browser

Guide example Β· Run to view results.

Expected result
rowsnon_nulldistinct_values
321

Watch for: After a LEFT JOIN, count(*) includes the preserved unmatched row. Count a non-null key from the joined table to count matches.

Full count() reference β†’

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.