sum

Applies to VAST Cluster 5.5.

Functions › Aggregate and analytic functions › Sums and averages. Adds up the non-NULL values of a number column or expression. On a BOOLEAN, it counts the true values.

Syntax

sum(x)

Returns: depends on the type of x:

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (ADBC), and work in a schema called docs_sql_fn_aggregate_sum. Set QE_ENDPOINT, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY and VASTDB_BUCKET (SQL reference), and run the blocks in order in one Python session. Below each block is the output you should see.

This block connects, defines the run() and plan() helpers, and creates a table of four order lines: a quantity, a unit price, a weight in kilograms, whether the line is paid, and the IDs of the items shipped. The last row has no values:

import os
import re

import adbc_driver_manager
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/docs_sql_fn_aggregate_sum"'

conn = dbapi.connect(
    driver=adbc_driver_vastdb.get_driver_path(),
    db_kwargs={
        "vast.db.endpoint": os.environ["QE_ENDPOINT"],
        "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
        "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
    },
    autocommit=True,
)

def server_message(e):
    """The server's message in a driver error, without the request ID or the echoed statement."""
    text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e))
    found = (re.search(r'message: "(.*?)", request_id', text, re.S)
             or re.search(r"description: (.*)\)", text, re.S))
    message = found.group(1) if found else text.strip().splitlines()[-1]
    message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S)
    message = re.split(r"\\n|\n", message)[0].replace('\\"', '"')
    return message.replace(BUCKET, "<bucket>")

def run(sql):
    """Run one statement. Print the rows it returns, or the error."""
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall() if cur.description else []
    except (adbc_driver_manager.Error, OSError) as e:
        print(f"{type(e).__name__}: {server_message(e)}")
    else:
        print(rows)

def plan(sql):
    """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert."""
    with conn.cursor() as cur:
        cur.execute("EXPLAIN " + sql)
        steps = cur.fetchall()[0][1]
    print("\n".join(line for line in steps.splitlines() if "Rows" not in line))

TABLE = f"{SCHEMA}.order_lines"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} "
    "(id BIGINT, qty INTEGER, price DECIMAL(10,2), weight DOUBLE, paid BOOLEAN, items BIGINT[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 2, 9.99, 1.5, true, [101, 102]), "
    "(2, 1, 24.50, 0.25, false, [103]), "
    "(3, 5, 3.10, 2.0, true, []), "
    "(4, NULL, NULL, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

sum(x) is an aggregate: it adds up the values of x over the rows of the query, skips the NULLs, and returns one value. x can be a column or an expression, such as qty * price or CASE WHEN ... THEN 1 ELSE 0 END.

The result type is wide enough that integer sums don't overflow in practice: a sum of BIGINT values is a 128-bit HUGEINT, so two rows at the largest BIGINT, 9223372036854775807, add up to 18446744073709551614 without an error. A DECIMAL(p, s) sum keeps the scale s and widens to 38 digits, and is exact: ten rows of 0.10 add up to 1.00. A DOUBLE sum is a floating-point sum: it can round, and it doesn't fail on overflow (see NULLs and edge cases).

On a BOOLEAN, true counts as 1 and false as 0, so sum(paid) is the number of paid lines.

NULLs and edge cases

The same cases, as the cluster returns them. They use the table and helpers from Set up the examples:

run(f"SELECT sum(qty), sum(price), sum(weight), sum(paid) FROM {TABLE}")
run(f"SELECT sum(qty) FROM {TABLE} WHERE id = 4")
run(f"SELECT sum(qty), coalesce(sum(qty), 0) FROM {TABLE} WHERE id > 100")
run("SELECT sum(NULL), sum(42)")
run(f"SELECT sum(9223372036854775807) FROM {TABLE}")
run("SELECT sum(x) FROM (SELECT 0.1::DOUBLE AS x FROM range(10))")
run("SELECT sum(x) FROM (SELECT 0.1::DECIMAL(10,2) AS x FROM range(10))")
run("SELECT sum(x) FROM (VALUES (1.0e308::DOUBLE), (1.0e308::DOUBLE)) v(x)")
run("SELECT sum(x) FROM (VALUES ('inf'::DOUBLE), ('-inf'::DOUBLE)) v(x)")
run("SELECT sum(x) FROM (VALUES (99999999999999999999999999999999999999::DECIMAL(38,0)), "
    "(99999999999999999999999999999999999999::DECIMAL(38,0))) v(x)")
run(f"SELECT sum(id * 9223372036854775807) FROM {TABLE}")
run(f"SELECT sum('10') FROM {TABLE}")
run(f"SELECT sum(DISTINCT qty) FROM {TABLE}")

[(Decimal('8'), Decimal('37.59'), 3.75, Decimal('2'))]
[(None,)]
[(None, Decimal('0'))]
[(None, Decimal('42'))]
[(Decimal('36893488147419103228'),)]
[(1.0,)]
[(Decimal('1.00'),)]
[(inf,)]
[(nan,)]
OSError: Overflow in HUGEINT addition: 99999999999999999999999999999999999999 + 99999999999999999999999999999999999999
OSError: Overflow in multiplication of INT64 (2 * 9223372036854775807)!
InternalError: Binder Error: No function matches the given name and argument types 'sum(VARCHAR)'. You might need to add explicit type casts.
InternalError: The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.

Examples

The total quantity, the total of the unit prices, the total weight and the number of paid lines:

run(f"SELECT sum(qty), sum(price), sum(weight), sum(paid) FROM {TABLE}")

[(Decimal('8'), Decimal('37.59'), 3.75, Decimal('2'))]

The value of all the lines, and of the paid ones only, from an expression. qty * price is an INTEGER times a DECIMAL(10,2), so the sum is a DECIMAL and exact:

run(f"SELECT sum(qty * price), sum(qty * price) FILTER (WHERE paid) FROM {TABLE}")

[(Decimal('59.98'), Decimal('35.48'))]

The number of items shipped over all the lines, and the total of their IDs. sum() doesn't take a list: unnest the lists into rows first, or use list_sum to add up the elements of each list. list_sum of an empty list is NULL:

run(f"SELECT count(*), sum(x) FROM (SELECT unnest(items) AS x FROM {TABLE})")
run(f"SELECT id, list_sum(items) FROM {TABLE} ORDER BY id LIMIT 10")

[(3, Decimal('306'))]
[(1, Decimal('203')), (2, Decimal('103')), (3, None), (4, None)]

How it runs in VAST

The Query Engine runs on the cluster's CNodes. A sum query has two steps, which EXPLAIN shows:

1. A scan in VAST DataBase (VAST_SCAN). It reads only the columns the sum uses, and evaluates the WHERE conditions it can push down, so rows that don't match never leave storage.
2. One aggregate step (UNGROUPED_AGGREGATE) in the Query Engine. It computes the expression inside sum() for each row the scan sends, adds it up, and returns one row.

The plan of a sum with a pushed-down WHERE condition. The condition doesn't appear in the plan, because the scan evaluates it:

plan(f"SELECT sum(weight) FROM {TABLE} WHERE id > 1")

┌───────────────────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│        sum(weight)        │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│    Projections: weight    │
│                           │
└───────────────────────────┘

Make it faster

• Filter in WHERE, not in FILTER (WHERE ...). sum(x) FILTER (WHERE id > 1) and sum(x) ... WHERE id > 1 give the same result, but the FILTER condition is evaluated in the aggregate step, after the scan has sent every row, and the WHERE condition in VAST DataBase. Use FILTER when you need different conditions for different sums in one query:

plan(f"SELECT sum(weight) FILTER (WHERE id > 1) FROM {TABLE}")

┌───────────────────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│ sum(weight) FILTER (WHERE │
│ (id > 1)) Filter: (id > 1)│
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             id            │
│           weight          │
│                           │
└───────────────────────────┘

• Keep functions out of the WHERE condition. WHERE abs(weight) > 1 isn't pushed down: the plan shows a FILTER step in the Query Engine above the scan. Compare the raw column with a constant when you can.
• Compute several sums in one query. They share one scan and one aggregate step.
• Sort the table on the column you filter by. Sorting lets the scan skip whole blocks of rows; see indexing and data skipping.

Limitations and errors

• Numbers and BOOLEAN only. A string, a date, a time, a timestamp, an interval or a list fails with No function matches the given name and argument types; the error lists the types sum takes. A number stored as text needs a cast: sum(CAST(s AS BIGINT)).
• Overflow inside the expression. sum() itself doesn't overflow on integers, but the expression inside it is computed in the column's type first: id * 9223372036854775807 overflows BIGINT before the sum sees it. Cast to DOUBLE first if an approximate result is enough: sum(id::DOUBLE * 9223372036854775807).
• DECIMAL(38, s) sums can overflow. A sum beyond 38 digits fails with Overflow in HUGEINT addition.
• DOUBLE sums round. Use a DECIMAL column for money and other values that must add up exactly. fsum, kahan_sum and sumkahan aren't available (not available).
• No GROUP BY or DISTINCT. A sum per group fails with The operator 'HASH_GROUP_BY' type=9 is currently not supported.; sum(DISTINCT x) fails with The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.
• Window forms. sum(x) OVER (ROWS UNBOUNDED PRECEDING) runs, as a running total in the order the scan returns the rows. OVER (), OVER (ORDER BY ...) and OVER (PARTITION BY ...) fail with The operator 'WINDOW' type=6 is currently not supported. See window functions.

run(f"SELECT sum(id::DOUBLE * 9223372036854775807) FROM {TABLE}")
run(f"SELECT sum(items) FROM {TABLE}")
run(f"SELECT paid, sum(qty) FROM {TABLE} GROUP BY paid")
run(f"SELECT id, sum(qty) OVER (ORDER BY id) FROM {TABLE}")

[(9.223372036854776e+19,)]
InternalError: Binder Error: No function matches the given name and argument types 'sum(BIGINT[])'. You might need to add explicit type casts.
InternalError: The operator 'HASH_GROUP_BY' type=9 is currently not supported.
InternalError: The operator 'WINDOW' type=6 is currently not supported.

Similar functions

run(f"SELECT sum(qty), avg(qty), product(qty), sum(CASE WHEN paid THEN 1 ELSE 0 END), count_if(paid) FROM {TABLE}")
run("SELECT list_sum([1, 2, 3])")

[(Decimal('8'), 2.6666666666666665, 10.0, Decimal('2'), Decimal('2'))]
[(Decimal('6'),)]

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

• Aggregate and analytic functions: all the aggregates, and the ones that aren't available.
• avg, count_if and FILTER.
• Pushdowns and EXPLAIN.