Skip to main content

SUM

Returns the sum of all non NULL values in a column or grouping of columns.

See also:
[SELECT] [TOTAL]

tip

Use the TOTAL function to sum floating point numbers or long values.

Syntax​

SELECT SUM(columnExpression) FROM <multipartIdentifier>
[ GROUP BY groupByColumn ];

Arguments​

columnExpression
A column or expression.

If all the values in the columnExpression are NULL then SUM returns NULL.

SUM will throw an "integer overflow" exception if an integer overflow occurs at any point during the computation. The [TOTAL]](/language-spec/functions/aggregate/total) function never throws an integer overflow.

groupByColumn
A column or columns used to perform summary or aggregate operations against. The GROUP BY clause returns one row for each column grouping.

Return Value(s)​

Returns an integer representing the sum of the columnExpression if all non NULL inputs are integers. If any inputs are not integers or are NULL then a floating point value which might be an approximation to the true sum is returned.

Examples​

Return the sum of a column expression over an entire resource​

SELECT sum(json_array_length(disks)) as sum_disks
FROM google.compute.instances 
WHERE project = 'stackql-demo' 
AND zone = 'australia-southeast1-a';

Return the sum of a column expression grouped by another column expression​

SELECT tags, sum(json_array_length(disks)) as sum_disks
FROM google.compute.instances 
WHERE project = 'stackql-demo' 
AND zone = 'australia-southeast1-a'
GROUP BY json_extract(tags, '$.instanceType');

Use SUM as a window function to calculate running totals​

-- Calculate running total and percentage of contributions
SELECT
    login,
    contributions,
    SUM(contributions) OVER (ORDER BY contributions DESC) as running_total,
    SUM(contributions) OVER () as total_contributions,
    ROUND(100.0 * contributions / SUM(contributions) OVER (), 2) as pct_of_total
FROM github.repos.contributors
WHERE owner = 'stackql'
  AND repo = 'stackql';

For more information, see https://www.sqlite.org/lang_aggfunc.html#sumunc.