All Products
Search

This Product

    MaxCompute:SUM

    Document Center

    MaxCompute:SUM

    Last Updated:Mar 26, 2026

    Returns the sum of non-null values. Use the DISTINCT keyword to sum only unique non-null values.

    SUM operates as both an aggregate function and a window function.

    Syntax

    Aggregate syntax

    DECIMAL|DOUBLE|BIGINT sum(<colname>)

    Window syntax

    sum([DISTINCT] <expr>) over ([partition_clause] [orderby_clause] [frame_clause])

    Parameters

    Aggregate mode

    ParameterRequiredDescription
    colnameYesThe column to sum. Accepts all data types. STRING values are implicitly converted to DOUBLE before calculation.

    Window mode

    ParameterRequiredDescription
    exprYesThe column to sum. Must be DOUBLE, DECIMAL, or BIGINT. STRING values are implicitly converted to DOUBLE; other types return an error. Null rows are excluded.
    partition_clause, orderby_clause, frame_clauseNoWindow definition. See windowing_definition.

    Return value

    Null rows are excluded from the calculation. The return type depends on the input type:

    Input typeReturn type
    TINYINTBIGINT
    SMALLINTBIGINT
    INTBIGINT
    BIGINTBIGINT
    FLOATDOUBLE
    DOUBLEDOUBLE
    DECIMALDECIMAL

    Usage notes

    Window function restrictions

    • Use window functions only in SELECT statements.

    • Window functions cannot contain nested window functions or nested aggregate functions.

    • Do not use window functions together with aggregate functions at the same level.

    Hive-compatible mode

    The ORDER BY clause behavior differs between modes:

    • Non-Hive-compatible mode (odps.sql.hive.compatible=false): For rows with the same ORDER BY value, each row receives its own cumulative sum up to that row's position.

    • Hive-compatible mode (odps.sql.hive.compatible=true): For rows with the same ORDER BY value, all tied rows receive the same cumulative sum — the total through the last tied row.

    Sample data

    The examples in this topic use a table named emp. Create the table and load the sample data:

    create table if not exists emp
   (empno bigint,
    ename string,
    job string,
    mgr bigint,
    hiredate datetime,
    sal bigint,
    comm bigint,
    deptno bigint);
tunnel upload emp.txt emp;

    The emp.txt file contains the following data:

    7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10

    Examples

    Example 1: Partition sum without ORDER BY

    Partition by deptno and sum sal for each partition. Without an ORDER BY clause, all rows in the same partition return the same total.

    select deptno, sal, sum(sal) over (partition by deptno) from emp;

    Result:

    +------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 17500      |   -- All rows in deptno=10 return the partition total.
| 10         | 2450       | 17500      |
| 10         | 5000       | 17500      |
| 10         | 1300       | 17500      |
| 10         | 5000       | 17500      |
| 10         | 2450       | 17500      |
| 20         | 3000       | 10875      |
| 20         | 3000       | 10875      |
| 20         | 800        | 10875      |
| 20         | 1100       | 10875      |
| 20         | 2975       | 10875      |
| 30         | 1500       | 9400       |
| 30         | 950        | 9400       |
| 30         | 1600       | 9400       |
| 30         | 1250       | 9400       |
| 30         | 1250       | 9400       |
| 30         | 2850       | 9400       |
+------------+------------+------------+

    Example 2: Running cumulative sum in non-Hive-compatible mode

    With ORDER BY and Hive-compatible mode disabled, SUM returns a cumulative sum from the first row of the partition to the current row. Rows with the same sal value each advance the cumulative total independently.

    -- Disable Hive-compatible mode.
set odps.sql.hive.compatible=false;
-- Calculate the running cumulative sum.
select deptno, sal, sum(sal) over (partition by deptno order by sal) from emp;

    Result:

    +------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300       |   -- First row of the partition.
| 10         | 1300       | 2600       |   -- Cumulative sum of rows 1-2.
| 10         | 2450       | 5050       |   -- Cumulative sum of rows 1-3.
| 10         | 2450       | 7500       |
| 10         | 5000       | 12500      |
| 10         | 5000       | 17500      |
| 20         | 800        | 800        |
| 20         | 1100       | 1900       |
| 20         | 2975       | 4875       |
| 20         | 3000       | 7875       |
| 20         | 3000       | 10875      |
| 30         | 950        | 950        |
| 30         | 1250       | 2200       |
| 30         | 1250       | 3450       |
| 30         | 1500       | 4950       |
| 30         | 1600       | 6550       |
| 30         | 2850       | 9400       |
+------------+------------+------------+

    Example 3: Running cumulative sum in Hive-compatible mode

    With ORDER BY and Hive-compatible mode enabled, all rows with the same ORDER BY value receive the same cumulative sum — the total through the last tied row.

    -- Enable Hive-compatible mode.
set odps.sql.hive.compatible=true;
-- Calculate the running cumulative sum.
select deptno, sal, sum(sal) over (partition by deptno order by sal) from emp;

    Result:

    +------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 2600       |   -- Both rows with sal=1300 get the sum through the second row.
| 10         | 1300       | 2600       |
| 10         | 2450       | 7500       |   -- Both rows with sal=2450 get the sum through the fourth row.
| 10         | 2450       | 7500       |
| 10         | 5000       | 17500      |
| 10         | 5000       | 17500      |
| 20         | 800        | 800        |
| 20         | 1100       | 1900       |
| 20         | 2975       | 4875       |
| 20         | 3000       | 10875      |
| 20         | 3000       | 10875      |
| 30         | 950        | 950        |
| 30         | 1250       | 3450       |
| 30         | 1250       | 3450       |
| 30         | 1500       | 4950       |
| 30         | 1600       | 6550       |
| 30         | 2850       | 9400       |
+------------+------------+------------+

    Example 4: Total sum of a column

    Calculate the total salary across all employees.

    select sum(sal) from emp;

    Result:

    +------------+
| _c0        |
+------------+
| 37775      |
+------------+

    Example 5: Sum grouped by department

    Use GROUP BY with SUM to calculate the total salary per department.

    select deptno, sum(sal) from emp group by deptno;

    Result:

    +------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 17500      |
| 20         | 10875      |
| 30         | 9400       |
+------------+------------+

    What's next