Number - Addition (or Sum, Sigma) or Total

> (Data|State) Management and Processing > (Data Type|Data Structure) > Number, Numeric, Quantity > Number - Function > Number - Arithmetic (Function|Operation)

1 - About

The addition of all numbers in a set is called a total.

Sum can be use as:

It can follow the full syntax of the analytic function and in this way create Running Total

Advertising

3 - Associative Property

An addition has an associative property and groups to the left (i.e., is left-associative). 1 + 2 + 3 is equivalent to (1 + 2 ) + 3

Example in Javascript

console.log(1 + 2 + 3); // 6
console.log((1 + 2) + 3); // 6
console.log(1 + "2" + 3); // 123
console.log((1 + "2") + 3); // 123

4 - Different Methods

An addition can be:

The two arithmetic operations work on different types of operands and require different functions.

5 - Sigma

The symbol “sigma” is the Greek capital S and is a shortcut for “the sum of”.

  • sum of x: sum{}{}{x}

6 - Analytic / Aggregate

The data come from the sample schema SH and the two next statements retrieve the same result.

6.1 - Aggregate (Group By)

SELECT DISTINCT CUST_LAST_NAME, SUM(AMOUNT_SOLD)  
FROM 
   SH.CUSTOMERS CUST,
   SH.SALES SALES
WHERE  ( CUST.CUST_ID = SALES.CUST_ID ) AND
( CUST_LAST_NAME LIKE 'Aa%' OR CUST_LAST_NAME LIKE 'Ab%' OR CUST_LAST_NAME LIKE 'Ac%' )
GROUP BY CUST_LAST_NAME
ORDER BY CUST_LAST_NAME ASC

6.2 - Analytic / Window (Over )

SELECT DISTINCT CUST_LAST_NAME, SUM(AMOUNT_SOLD)  OVER (ORDER BY CUST_LAST_NAME)
FROM 
   SH.CUSTOMERS CUST,
   SH.SALES SALES
WHERE  ( CUST.CUST_ID = SALES.CUST_ID ) AND
( CUST_LAST_NAME LIKE 'Aa%' OR CUST_LAST_NAME LIKE 'Ab%' OR CUST_LAST_NAME LIKE 'Ac%' )
ORDER BY CUST_LAST_NAME ASC

6.3 - Analytic and Aggregate Statement

SELECT 
    DISTINCT CUST_LAST_NAME, 
    SUM(AMOUNT_SOLD) ,
    SUM(SUM(AMOUNT_SOLD) OVER () -- Grand Total. You must give the two times the SUM function. Otherwise you get a ORA-00979.
FROM 
   SH.CUSTOMERS CUST,
   SH.SALES SALES
WHERE  ( CUST.CUST_ID = SALES.CUST_ID ) AND
( CUST_LAST_NAME LIKE 'Aa%' OR CUST_LAST_NAME LIKE 'Ab%' OR CUST_LAST_NAME LIKE 'Ac%' )
GROUP BY CUST_LAST_NAME
ORDER BY CUST_LAST_NAME ASC

7 - Identity

The identity function for addition (+) is <MATH> x+0 = x </MATH>

8 - Reference

Advertising
data/type/number/function/primitive/addition.txt · Last modified: 2019/08/25 11:39 by gerardnico