Various Miscellaneous Functions are avaialble in Teradata. For example: COALESCE, TYPE, XMLAGG, MAX, MIN, SUM etc.

Teradata Miscellaneous Functions

Teradata provides many functions which can be used to fulfil various user requirements, click on the required function to get detail

• COALESCE
• TYPE
• XMLAGG

• COALESCE: This will return first not null value available in the parameter list,any number of arguments can be passed.

  
  SELECT COALESCE(NULL,NULL,'Teradata');
  Output: Teradata
  
  SELECT COALESCE(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1);
  Output: 1
  

  
  
• TYPE: This will return datatype of the passed argument.

  
  SELECT TYPE('Teradata');
  Output: VARCHAR(8) CHARACTER SET UNICODE
  
  SELECT TYPE(454.44);
  Output: DECIMAL(5,2)
  
  SELECT TYPE(current_time);
  Output: TIME(0) WITH TIME ZONE 
  
  SELECT TYPE(current_date);
  Output: DATE
  

  
  
• XMLAGG: This functions performs aggregation of multiple rows, this can used for multiple purpose such as constructing XML value or merging rows string values into a single row value. Return datatype of XMLAGG function will be SYSUDTLIB.XML

  
  Syntax: 
  XMLAGG(column_to_merge [, ORDER BY sort_column_name [DESC|ASC])
  

  
  
  Creating Table: Creating a table and inserting sample data for illustrating XMLAGG function.

  
  CREATE MULTISET VOLATILE TABLE xmlaggr_example
   (
    id INTEGER,
    code CHAR(1)
   )
   PRIMARY INDEX(id)
  ON COMMIT PRESERVE ROWS;
  
  
  INSERT INTO xmlaggr_example(1,'A');
  INSERT INTO xmlaggr_example(1,'B');
  INSERT INTO xmlaggr_example(1,'C');
  INSERT INTO xmlaggr_example(1,'D');
  

  
  Example 1: Merging string from multiple rows into one column using XMLAGG function.

  
  SELECT id,  
   TRIM(TRAILING ',' FROM (XMLAGG(code||',' ORDER BY code DESC) (VARCHAR(100)))) merged_column
  FROM xmlaggr_example
  GROUP BY 1
  
  id  merged_column
  --  --------------
  1   D, C, B, A
  

  Points to Note
  1. Concatenation of comma (||',') is used with column to specify delimiter between row values.
  2. Trim is used to remove extra comma(,) in the end of merged columns, otherwise output will be like "A, B, C, D,"
  3. Output of XMLAGG function is explicitly typecasted to VARCHAR before passing it to string function TRIM. This is done because XMLAGG function return value of type SYSUDTLIB.XML.
  
  
  

1. These functions cannot be used in WHERE clause of SELECT statement but can be used in WHERE clause of ABORT statement.
2. HAVING clause can be used to filter rows based on these functions

• MAX: Evaluate maximun value for the given column within the table or group if specified.

  
  SELECT MAX(salary) FROM tutorial_db.employee;
  Output: 5000.00
  
  
  SELECT dept_no, MAX(salary) FROM tutorial_db.employee GROUP BY dept_no; --Maximun salary in each depatment
  Output: 
             dept_no       Maximum(salary)
         -----------  --------------------
                 100               5000.00
                 200               3000.00
                 300               2850.00
  

  
  
• MIN: Evaluate minimum value for the given column within the table or group if specified.

  
  SELECT MIN(salary) FROM tutorial_db.employee;
  Output: 800.00
  
  
  SELECT dept_no, MIN(salary) FROM tutorial_db.employee GROUP BY dept_no; --Minimum salary in each depatment
  Output: 
             dept_no       Minimum(salary)
         -----------  --------------------
                 100               1300.00
                 200                800.00
                 300                950.00
  

  
  
• SUM: Addition of all row ignoring null for the given column within the table or group if specified.

  
  SELECT SUM(salary) FROM tutorial_db.employee;
  Output: 29025.00
  
  
  SELECT dept_no, SUM(salary) FROM tutorial_db.employee GROUP BY dept_no; --Sum of salary in each depatment
  Output: 
             dept_no           Sum(salary)
         -----------  --------------------
                 100               8750.00
                 200              10875.00
                 300               9400.00
  

  
  
• AVG: Returns average that means addition of all row ignoring null for the given column divided by count of rows within the table or group if specified.

  
  SELECT AVG(salary) FROM tutorial_db.employee;
  Output: 2073.21
  
  
  SELECT dept_no, AVG(salary) FROM tutorial_db.employee GROUP BY dept_no; --Average salary in each depatment
  Output: 
             dept_no       Average(salary)
         -----------  --------------------
                 100               2916.67
                 200               2175.00
                 300               1566.67
  

  
  
• COUNT: Returns average that means addition of all row ignoring null for the given column divided by count of rows within the table or group if specified.

  
  SELECT COUNT(*) FROM tutorial_db.employee;
  Output: 14
  
  SELECT COUNT(commission) FROM tutorial_db.employee; --If column is mentioned in count then null will be ignored in counting
  Output: 4
  
  SELECT dept_no, COUNT(salary) FROM tutorial_db.employee GROUP BY dept_no; --Count of employees by department
  Output: 
            dept_no     Count(*)
        -----------  -----------
                100            3
                200            5
                300            6