sum ​

Calculates the sum of numeric values. NULL values do not contribute to the sum.

Syntax

sum(num)

Arguments

• num — A numeric column or expression. Integers, floating-point numbers, and decimals are supported. Legacy text and blob values use SQL numeric conversion. Native dates, times, intervals, addresses, and collections require an explicit numeric conversion.

Returned value

Returns the sum of the non-NULL values, or NULL for empty or all-NULL input.

• Ordinary integers produce an INTEGER result. Exact overflow raises NUMERIC_OVERFLOW.
• Floating-point inputs produce a REAL result.
• Native unsigned and wide integers retain an exact numeric result within their supported range.
• Decimals produce an exact decimal total. Mixing decimals with approximate inputs raises TYPE_MISMATCH; cast the inputs to one numeric family first.

Use coalesce(sum(num), 0) when an empty result should be zero. total(num) always produces a floating-point total and returns 0.0 for empty input.

Examples

Computing sum of employee salaries

CREATE TABLE employees
(
    id UInt32,
    name String,
    salary UInt32
);

INSERT INTO employees VALUES
    (87432, 'John Smith', 45680),
    (59018, 'Jane Smith', 72350),
    (20376, 'Ivan Ivanovich', 58900),
    (71245, 'Anastasia Ivanovna', 89210);

SELECT sum(salary) FROM employees FORMAT csv;

266140

Total time spent on requests

Store the duration as a numeric number of milliseconds:

CREATE TABLE requests (req_id UInt32, duration_ms UInt32);
INSERT INTO requests VALUES (1, 100), (2, 200), (3, 300);

SELECT sum(duration_ms) AS total_duration_ms FROM requests FORMAT csv;

600

Missing values and empty input

INSERT INTO requests VALUES (4, NULL);

SELECT sum(duration_ms) FROM requests FORMAT csv;

600

SELECT sum(duration_ms), coalesce(sum(duration_ms), 0)
FROM requests
WHERE req_id > 100
FORMAT csv;

\N,0