Common Aggregate Functions

An aggregate function performs a calculation on a set of values, and returns a single, aggregated value. Aggregate functions are often used with the GROUP BY clause of the SELECT statement. Click here for a full list of aggregate functions.

any()

The any() function returns the first unique value encountered. Using any() in your SQL query can significantly improve it's performance.

Example any()

To understand how the any() function works, insert the transaction hash from the table above into the WHERE statement.

Let's assume you want to find the daily sum of gas paid on Ethereum - denominated in ETH. Due to the structure of the  all_chains table, each log event has a separate row; meaning the transaction information connected to that log event gets printed each time.

To resolve this in your query, you can use the any() function like so:

sql

SELECT date_trunc('day', signed_at) as date
       , sum(tx.tx_gas_spent/pow(10, 18)* toFloat64(tx.tx_gas_price)) as aggregate_gas_cost
FROM (
    SELECT any(tx_hash), tx_gas_spent, tx_gas_price, signed_at
    -- this fetches the gas spent for every transaction and only one row for each
      FROM blockchains.all_chains
      WHERE chain_name = 'eth_mainnet'
        AND signed_at > '2024-01-01'
      GROUP BY tx_gas_spent, tx_gas_price, signed_at
    ) tx
 GROUP BY date

argMax()

argMax(arg, val) calculates the arg value for the maximum value of val.

Example argMax()

Lets assume you want to add a metric chart to your dashboard to display the transaction count for the month. To start, the query below counts the number of unique transactions per month over the last three months.

sql

SELECT uniq(tx_hash) as count, date_trunc('month', signed_at) as month
FROM blockchains.all_chains
WHERE signed_at > now() - interval '3 month'
GROUP BY month

The query returns two columns - count and month - with 4 rows of data

However, all you want to display is the transaction count for this month. To do so, use the argMax() function, which finds the transaction count for the maximum (i.e., latest) month. Your query would look something like this:

sql

SELECT argMax(count, month)
FROM (
    SELECT uniq(tx_hash) as count, date_trunc('month', signed_at) as month
    FROM blockchains.all_chains
    WHERE signed_at > now() - interval '3 month'
    GROUP BY month
  )

min()

The min() function calculates the minimum across a group of values.

uniq()

SELECT count(distinct value) is a commonly used to find a count of unique values. However, the distinct statement is extremely inefficient as it compares every value with every other value. The uniq() function is a solution to this inefficiency as it returns a very accurate approximate count of unique values. Using 'uniq(x)` in your SQL query can significantly improve it's performance.