This aggregate function executes an addition operation on the numbers in the reference column (the column to which sum is applied) as all duplicate rows are collapsed into unique single rows. Excluding the column to which the sum is being applied, all rows are examined column-by-column in order to find identical rows. Note that attempts to apply sum to a text value in the reference column will result in a 0 for that row. The header of the reference column receives a name change to become “Sum of <column name>”.
Examples
The small dataset below will be used to show how sum operates. All numbers are numeric values (not text) in order to illustrate behavior of this aggregate function.
| Column A | Column B | Column C |
|---|
| 1 | two | 5 |
| 1 | two | 6 |
| two | two | 7 |
| 1 | two | 4 |
Example 1
Applying the sum function to Column C reduces the row count from four to two. The value in the column Sum of Column C shows the sum of of the Column C values in the duplicate rows that were collapsed during the operation: 5 + 6 + 4 = 15 while 7 + 0 = 7.
| Column A | Column B | Sum of Column C |
|---|
| 1 | two | 15 |
| two | two | 7 |
Example 2
Applying the sum function to Column A results in no row count reduction since examination of Column B and Column C values in each row reveals that each is already unique. The value in the column Sum of Column A therefore displays each row with its original numeric value (in this case, a number 1) since the sum function found no duplicate rows that could be involved in an adding operation. The 0 replaces the text “two” because the sum operation cannot be applied to text values.
| Sum of Column A | Column B | Column C |
|---|
| 1 | two | 5 |
| 1 | two | 6 |
| 1 | two | 4 |
| 0 | two | 7 |
日本語
