SUM¶

Computes the sum of all non-NULL values in a group.

Syntax¶

Parameters¶

SUM(expression): Sums all non-NULL values of expression in the group. NULL values are ignored.

Returns¶

Returns a BIGINT representing the sum of all non-NULL values. If all values are NULL or the input set is empty, returns NULL.

Examples¶

Setup¶

For these examples, assume we have a sales table:

CREATE TABLE sales(
    id BIGINT,
    product STRING,
    region STRING,
    amount BIGINT,
    PRIMARY KEY(id))

INSERT INTO sales VALUES
    (1, 'Widget', 'North', 100),
    (2, 'Widget', 'South', 150),
    (3, 'Gadget', 'North', 200),
    (4, 'Gadget', 'South', NULL),
    (5, 'Widget', 'North', 120)

SUM - Sum All Values¶

Sum all amounts in the table:

SELECT SUM(amount) AS total_amount FROM sales

`total_amount`
`570`

Notice that the NULL value in row 4 is ignored in the sum.

SUM with GROUP BY¶

Sum amounts per product:

SELECT product, SUM(amount) AS total_amount
FROM sales
GROUP BY product

`product`	`total_amount`
`"Widget"`	`370`
`"Gadget"`	`200`

Sum amounts per region:

SELECT region, SUM(amount) AS total_amount
FROM sales
GROUP BY region

`region`	`total_amount`
`"North"`	`420`
`"South"`	`150`

The South region sum only includes the non-NULL value (150), ignoring the NULL from the Gadget sale.

Important Notes¶

Index Requirement: GROUP BY queries require an appropriate index to execute. See Indexes for details on creating indexes that support GROUP BY operations.