SUM

SUMreturns the sum of values of an expression over a set of rows. You can use it as an window (analytic) function.

The SUM function function takes as an argument any numeric data type or any non-numeric data type that can be implicitly converted to a numeric data type. The function returns the same data type as the numeric data type of the argument.

Syntax

SUM ( [ DISTINCT | ALL ] Expression )

DISTINCT

If this qualifier is specified, duplicates are eliminated

If you specify DISTINCT in the analytic version of SUM, the OVER clause for your window function cannot include an ORDER BY clause or a frame clause.

ALL

If this qualifier is specified, all duplicates are retained. This is the default value.

Expression

An expression that evaluates to a numeric data type: SMALLINT.

An Expression can contain multiple column references or expressions, but it cannot contain another aggregate or subquery.

If an Expressionevaluates to NULL, the aggregate skips that value.

Usage

The Expression can contain multiple column references or expressions, but it cannot contain another aggregate or subquery. It must evaluate to a built-in numeric data type. If an expression evaluates to NULL, the aggregate skips that value.

Only one DISTINCT aggregate expression per Expression is allowed. For example, the following query is not valid:

   -- query not allowed
SELECT AVG (DISTINCT flying_time),
  SUM (DISTINCT miles)
  FROM Flights;

Note that specifying DISTINCT can result in a different value, since a smaller number of values may be summed. For example, if a column contains the values 1, 1, 1, 1, and 2, SUM(col) returns a greater value than SUM(DISTINCT col).

Results

The resulting data type is the same as the expression on which it operates (it might overflow).

Aggregate Examples

These queries compute the total of all salaries for all teams, and then the total for each individually.

splice> SELECT SUM(Salary) FROM Salaries;
1
--------------------
277275362

1 row selected
splice> SELECT SUM(Salary) FROM Salaries JOIN Players ON Salaries.ID=Players.ID WHERE Team='Cards';
1
--------------------
97007230

1 row selected
splice> SELECT SUM(Salary) FROM Salaries JOIN Players ON Salaries.ID=Players.ID WHERE Team='Giants';
1
--------------------
180268132

1 row selected

Analytic Example

This example computes the running total of salaries, per team, counting only the players who make at least $5 million in salary.

splice> SELECT Team, DisplayName, Salary,
   SUM(Salary) OVER(PARTITION BY Team ORDER BY Salary ASC
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "Running Total"
   FROM Players JOIN Salaries ON Players.ID=Salaries.ID
   WHERE Salary>5000000
   ORDER BY Team;

TEAM     |DISPLAYNAME             |SALARY              |RUNNING TOTAL
---------------------------------------------------------------------
Cards    |Larry Lintos            |7000000             |7000000
Cards    |Jack Hellman            |8300000             |15300000
Cards    |James Grasser           |9375000             |24675000
Cards    |Yuri Milleton           |15200000            |39875000
Cards    |Mitch Hassleman         |17000000            |56875000
Giants   |Jalen Ardson            |6000000             |60000000
Giants   |Steve Raster            |6000000             |12000000
Giants   |Marcus Bamburger        |6950000             |18950000
Giants   |Mark Briste             |8000000             |26950000
Giants   |Jack Peepers            |9000000             |35950000
Giants   |Alex Paramour           |10250000            |46200000
Giants   |Thomas Hillman          |12000000            |58200000
Giants   |Buddy Painter           |17277777            |75477777
Giants   |Tam Lassiter            |18000000            |93477777
Giants   |Harry Pennello          |18500000            |111977777
Giants   |Martin Cassman          |20833333            |132811110

16 rows selected

See Also