Grand Aggregate SQL Functions

All Grand Aggregate functions produce a scalar result of the specified operation on the input sequence.

seq_count()	Count of all elements
seq_max()	Maximum element value
seq_min()	Minimum element value
seq_sum()	Sum of all elements
seq_prd()	Product of all elements
seq_avg()	Average of all elements
seq_var()	Variance of elements
seq_var_samp()	Sample Variance of elements
seq_dev()	Standard Deviation of elements
seq_dev_samp()	Sample Standard Deviation of elements
seq_approxdc()	Approximate count of distinct values
seq_empty()	Return true if the sequence is empty; otherwise false

Example

Following is an example code snippet demonstrating some of the grand aggregate functions:

         
    -- count, max, min
            
    INSERT INTO SimpleSequence(testNumber,iVal1)
        VALUES(1,'{-3,-2,-1,0,1,2,3}');
 
    SELECT iVal1, seq_count(iVal1) AS "count", seq_max(iVal1) AS "max", 
        seq_min(iVal1) AS "min"
    FROM SimpleSequence WHERE testNumber = 1;
     
    iVal1{}  
     count 
      max 
        min
    -------------------------------------------------------------------------
    {-3, -2, -1, 0, 1, 2, 3} 
           7       
    3 
          -3
     
    -- _sum, _prd, _avg, _var, _dev
            
    INSERT INTO SimpleSequence(testNumber,fVal1)
        VALUES(3,'{1.1,2.2,3.3}');
     
    SELECT fVal1, seq_sum(fVal1) AS "sum", seq_prd(fVal1) AS "prd", seq_avg(fVal1) AS "avg",
        seq_var(fVal1) AS "var", seq_dev(fVal1) AS "dev"
    FROM SimpleSequence WHERE testNumber = 3;
     
    fVal1{}   
    sum     
    prd     
    avg     
    var 
        dev
    -------------------------------------------------------------------------
    {1.10000002384186, 2.20000004768372, 3.29999995231628} 
     6.60000002384186 
           7.98600023078918        
    2.20000000794729        
    0.806666614214581       
    0.898146209820306
     
    -- _approxdc
            
    INSERT INTO SimpleSequence(testNumber,fVal1)
        VALUES(4,'{1.1,1.1,2.2,3.3}');
     
    SELECT fVal1, seq_approxdc(fVal1) AS "approxdc"
    FROM SimpleSequence WHERE testNumber = 4;
     
    fVal1   
    approxdc
    -------------------------------------------------------------------------
    {1.10000002384186, 1.10000002384186, 2.20000004768372, 3.29999995231628} 
           3
 
    -- _empty
            
    INSERT INTO SimpleSequence(testNumber,fVal1,iVal1)
        VALUES(5,'{1.1,1.1,2.2,3.3}', '{}');
 
    SELECT seq_empty(iVal1) AS "iVal1 empty?", seq_empty(fVal1) AS "fVal1 empty?"
    FROM SimpleSequence WHERE testNumber = 5;
 
    iVal1 empty? 
       fVal1 empty?
    -------------------------------------------------------------------------
    true    
    false