What are AGGREGATE FUNCTIONS - IBM Mainframe

In order to get the answers for questions like 'How many employees are there in the Accounts Department?' or 'What is the average salary of a Systems Analyst?' or' Who is the highest paid employee and what is his salary?' etc. The simple SELECT statement with all the features that we have so far mentioned is not adequate. So we have a set of functions, aggregate or column functions, which greatly enhances the power of the SQL statements. The aggregate functions supported by DB2 are COUNT, SUM, AVG, MAX, and MIN. The function performed by each of the above mentioned aggregate functions are:

  • COUNT - To count the number of values in a column.
  • SUM - To find the sum of the values in a column.
  • AVC - To find the average of the values in a column.
  • MAX - To find the maximum value in a column.
  • MIN - To find the minimum value in a column.

The general syntax of the aggregate functions is as follows:

general syntax of the aggregate functions

These are powerful tools and can improve the data retrieval power considerably. There are some rules, which must be followed while using these functions. They are:

  • For SUM and AVG the argument must be of type numeric.
  • Except for the special case COUNT (*), the argument may be preceded by the key word DISTINCT to eliminate the duplicate rows before the function is applied to a column. The alternative to DISTINCT is ALL, which is the default. The DISTINCT is legal for MAX and MIN but meaningless.
  • The special function COUNT (*) that is used to all rows without any duplicate elimination and so the key word DISTINCT is not allowed for this function.
  • The argument cannot involve any aggregate function references or table expressions at any level of nesting. For example the SQL ' SELECT AVG (MIN (QTY)) AS AVERAGE' is illegal.
  • Any NULLs in the column is eliminated before the function is applied, regardless of whether DISTINCT is specified or not except in the case of COUNT (*) where nulls are handled like normal values.

If the argument happens to be an empty set, for example there are no values in the column, then COUNT returns a value of Zero, all other functions return null. This is an important point because, if the fact that the aggregate function will return a null if an empty set is encountered is not taken into consideration the program will abend when such a situation occurs during run time. For example in the SQL 'SELECT SUM (QTY) INTO :WS-SUM WHERE PART='P" and if there are no rows satisfying the where clause then the a null is returned and since there is no provision to take care of this DB2 will abend giving an abend code -305 [ The null value cannot be assigned to output host variable number position-no because no indicator variable is specified ] SQL code -305 is returned if the SQL returns a null value but there is no null indicator specified in the SQL statement for the host variable that receives the null value. The summarizing functions like SUM, AVG, MAX, MIN return a null value if there are no rows in the collection that satisfy the condition of the SQL statement. Only COUNT function is ar» exception, which returns a value 0 even if there are no rows satisfying the condition clause. If this condition arises then you must define a null indicator variable (Halfword binary) and use it in the SQL statement with the host variable accepting the results.

SQL statement with the host variable accepting the results

Given below are some examples which uses the aggregate functions:

  • SELECT COUNT (*) FROM employee-table [This will get the number of rows in the employee table]
  • SELECT COUNT (*) FROM employee-table WHERE dept ='CONSULTANCY [This will get the number of rows in the employee table which satisfies the WHERE clause.]
  • SELECT COUNT (DISTINCT suppliers) FROM shipment-table [Will get the number of suppliers after eliminating the duplicates.
  • SELECT SUM (salary) FROM employee-table [This will get the sum of the salaries of all the employees.].
  • SELECT AVG (salary) FROM employee-table WHERE dept ='ACCOUNTS' [This SQL will get the average salary of the accounts department]
  • SELECT AVG (salary) GROUP BY dept [Will get the average salary of all the departments].
  • SELECT name FROM employee-table WHERE salary > ALL (SELECT AVG (salary) GROUP BY dept) [This query will first find the average of all the department salaries and then get the names of all the employees whose salary is greater than the maximum of the average of the departmental salaries].
  • SELECT name FROM employee-table WHERE salary> ANY (SELECT AVG (salary) GROUP BY dept) [This query will first find the average of all the departmental salaries as before and the get the names of all the employees whose salary is greater than any of the departmental averages.]
  • SELECT dept, AVG (salary), MAX (salary), MIN (salary) FROM employee-table WHERE dept 'ACCOUNTS' GROUP BY dept [This SQL will get the department name, the average. maximum and minimum salaries of all the departments other than the accounts department. Here the important point is that the rows that do not satisfy the WHERE clause condition are eliminated before any grouping is done.]
  • SELECT dept, AVC (salary), MAX (salary), MIN (salary) FROM employee table CROUP BY dept HAVING COUNT(*)>250 [ This SQL will get the department name, the average, maximum and minimum salaries of all the departments which has a strength more than 250.]

HAVING is to groups what WHERE is to rows. Thus if HAVING is specified, GROUP BY clause also must be specified. Actually it is possible, even though unusual, to omit the GROUP BY clause, in which case the entire table will be considered as a single group. HAVING is used to eliminate groups just as WHERE is used to eliminate rows.

The Existential Quantifier

In SQL, an existentially qualified condition is represented by an expression of the form 'EXISTS (SELECT FROM.. )'. Such an expression evaluates to true only if the result of evaluating the subquery represented by the (SELECT FROM ) is non-empty. Or in other words, only if there exists a row in the table which satisfies the subquery.

The Existential Quantifier

In the above SQL, first the subquery SELECT * FROM department-table WHERE dept LIKE 'C%' is evaluated first and if there are any rows satisfying the subquery then the first part is .evaluated, which will fetch the names and salaries of all the employees whose department name starts with 'C. Similar to the EXISTS we can use NOT EXISTS also. For example if you want to get all the employee names whose department strength is less than 25 the SQL can be as follows:

The Existential Quantifier

In the above SQL the sub-query will get all the departments whose strength is more than 25 and the NOT EXISTS clause will get the employee names who does not belong to those departments. Another example of the NOT EXISTS using the supplier-parts-shipment is given below. 'Get the supplier names for suppliers such that there does not exist a part that they do not supply' or in other words 'Select the supplier names where for all parts there exists a shipment saying that the supplier supplies the part'.

supplier names where for all parts


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM Mainframe Topics