Aggregate Functions

What are aggregate functions

• Aggregate functions are also called column functions.
• A column function receives a set of values from a group of rows and produces a single value.
• They work down a column of values, not across a single row.
• They are mostly used in reports for totals, averages, counts, minimums and maximums.

The main aggregate functions

• COUNT - counts rows, or counts the non-null values of a column.
• SUM - adds up the values of a numeric column.
• AVG - calculates the average of a numeric column.
• MIN - returns the smallest value in the column.
• MAX - returns the largest value in the column.
• DB2 also supports STDDEV for standard deviation.

Rules you must remember

• DISTINCT can be used with SUM, AVG and COUNT. The function then works only on the unique values in the column.
• SUM and AVG cannot be used on non-numeric data types like CHAR or DATE, whereas MIN, MAX and COUNT can.
• NULL values are ignored by aggregate functions, except by COUNT(*).
• COUNT(*) counts all rows including NULLs. COUNT(column) skips the NULL values.
• Example:-
  -- Total salary of all A-1-2 grade employees SELECT SUM(Sal) FROM Employee WHERE Grade = 'A-1-2'; -- Average, maximum and minimum salary in one query SELECT AVG(Sal), MAX(Sal), MIN(Sal) FROM Employee WHERE Grade = 'A-1-2'; -- Number of rows, and number of distinct domains SELECT COUNT(*) FROM Employee; SELECT COUNT(DISTINCT Domain) FROM Employee;