Column function expressions can be used in the SELECT clause to perform calculations. We have a table: EMPLOYEE_TBL

If we wanted to know the difference between the minimum and maximum RATE_HOUR, we could do the following:

                    SELECT (MAX(RATE_HOUR) - MIN(RATE_HOUR))
                      FROM EMPLOYEE_TBL;

Result Table:

                   

Calculations are evaluated in the following order:
1. Expressions within parentheses with the innermost first.
2. Multiplication and division.
3. Addition and subtraction.
4. Left to right.

Column functions can be SELECTed with other column functions because they each return only one value.
Column functions can not be combined with SELECTed columns or scalar functions because, they return multiple rows.

If we have a table: INVENTORY_TBL

          SELECT MIN(PEOPLE)      AS MIN,
                       MAX(PEOPLE)     AS MAX,
                       AVG(PEOPLE)      AS AVG,
                       COUNT(PEOPLE) AS NOT_NULL,
                       COUNT(*)           AS ALL
             FROM INVENTORY_TBL;

Result Table:

 

COUNT(expression) counts the number of rows selected that are not null.
COUNT(*) counts all of the rows, including those that are null.
The expression is a column name, numeric expression, or literal.
Nulls are included in the count with COUNT(*), otherwise Nulls are removed from the count.
The argument values can be character, numeric, datetime, or asterisk (*).
The result will be a numeric integer of zero value or greater.
The keyword, DISTINCT, can be used, except when using COUNT(*).

 

          COUNT(expression)                     <- Count the number of non-nulls.

                              or

          COUNT(DISTINCT column-name) <- Count the number of unique non-nulls.

                              or

          COUNT(*)                                      <- Count the number of null and non-nulls.

 

Notes:
COUNT(*) - COUNT(expression)                        <- Gives the number of nulls in a column.
((COUNT(*)-COUNT(expr))*100)/COUNT(*)        <- Gives the percentage of nulls.

The MAX function returns the maximum value of a collection of values.
The expression is a column name, numeric expression, or literal.
Null values are removed.
The argument values can be character, numeric, or datetime.
The result can be character, numeric, datetime, or NULL, if there are no values.
The minimum of no rows is NULL, not zero.
The keyword, DISTINCT, can be used, but it serves no purpose and it doesn’t change the results.
The result is only as precise as the data types used in the expression.
The maximum with character data means the highest sort order. See ORDER BY.

 

                    MAX(expression)

                              or

                    MAX(DISTINCT column-name) <– No difference in the result.

 

To SELECT other columns associated with the MAX row, see the HAVING clause.

The MIN function returns the minimum value of a collection of values.
The expression is a column name, numeric expression, or literal.
Null values are removed.
The argument values can be character, numeric, or datetime.
The result can be character, numeric, datetime, or NULL, if there are no values.
The minimum of no rows is NULL, not zero.
The keyword DISTINCT can be used, but it serves no purpose and it doesn’t change the results.
The result is only as precise as the data types used in the expression.
The minimum with character data means the lowest sort order. See ORDER BY.

                                     MIN(expression)

                                               or

                              MIN(DISTINCT column-name) <– No difference in the result.

 To SELECT other columns associated with the MIN row, see the HAVING clause.

The AVG function returns the average, or arithmetic mean, of a collection of numbers.
The arithmetic mean is the sum of the values divided by the number of quantities.
The expression is a column name, numeric expression, or literal that only involves numeric values or nulls. Null values are removed.
The average of no rows is NULL, not zero.
DISTINCT will remove duplicate values.
The result is only as precise as the data types used in the expression.

                                                   AVG(expression)

                                                             or

                                         AVG(DISTINCT column-name)

The SUM function returns the summation total of a collection of numbers.
The expression is a column name, numeric expression, or literal that only involves numeric values or nulls. Null values are removed.
The sum of no rows is NULL, not zero.
DISTINCT will remove duplicate values.
The result is only as precise as the data types used in the expression.

                                                  SUM(expression)

                                                             or

                                        SUM(DISTINCT column-name)

Because a column function returns only a single value in the result table, a column function can only be included with other column functions, and can not be included with a condition that would result in many rows.

 <– is an invalid operation.

The one exception to this rule is a GROUP BY clause, where you use the GROUP BY column in the SELECT.

          SELECT COST_PCT, AVG(PRICE)
            FROM INVENTORY_TBL
            GROUP BY COST_PCT;

You can not nest column functions.

          SELECT SUM(MIN(QTY))           <-- is an invalid operation.

Oracle allows some exceptions to this rule.

You can use more than one column function in a SELECT statement.

          SELECT MIN(QTY), MAX(QTY)      <-- is ok.

You can not place a column function in a WHERE clause.

          SELECT DESCRIPTION
            FROM INVENTORY_TBL
           WHERE PRICE = MAX(PRICE)      <-- is an invalid operation.

 

A column function consists of the function name, followed by a pair of parentheses. Inside the parentheses is an argument, which is a column name, literal, scalar function or a combination of these. For example:

          SELECT AVG(PRICE) FROM INVENTORY_TBL

                                                or

          SELECT MIN(YEAR(HIRE_DATE),4,0)
            FROM EMPLOYEE_TBL

If the argument is a decimal column, then the result is decimal. If the argument is integer, then the result is integer and any fractional part is lost. The result of a column function does not have a heading in the result table, because it is not an existing and unmodified column in the base table.

If you use the keyword, DISTINCT, with a column function, then SQL will eliminate duplicate values before the column function is performed. Then the column function will be performed on every unique value that is SELECTed or is in the WHERE condition, rather than with the duplicate values.

NULL values are not considered in the calculation of a column function, except COUNT(*). Any NULL values are removed before the column function is performed. If these values are to be counted, then the column must be defined as NOT NULL, so that a default value of zeroes or spaces will be used in the column function. Alternatively, you can use COALESCE() in a column function to substitute a value for a null.

> DB2 & SQL Server omit the key word DATE, TIME, and TIMESTAMP ahead of the literal.
> Access surrounds date-time literals with the pound sign (#), so that the standard SQL date
DATE ‘2056-02-20′ is the same as the Access’s #2056-02-20#.

 

Built-in functions automate common processes and are identified either as column functions or as scalar functions.

Column functions (sometimes known as aggregate functions) perform an operation on all SELECTed rows of an entire column and returns a single value in the results table for each of the following:
> All rows in the table
> Only the rows specified in the WHERE clause
> The rows created by a GROUP BY are often used with a HAVING clause to filter the groups.
Column functions can only be in a SELECT or HAVING clause, not in a WHERE clause.

Column Functions:

SUM(expr) .... Summation of values in a column .... (Works with numeric data only)
AVG(expr) .... Average of values in a column ...... (Works with numeric data only)
MIN(expr) .... Minimum value in a column .......... (Character, numeric, or datetime)
MAX(expr) .... Maximum value in a column .......... (Character, numeric, or datetime)
COUNT(expr) .. Count the number of non-null rows .. (Works with all data)
COUNT(*) ..... Count the total number of rows ..... (Works with all data)

(expr) is usually a column name but can be a function, literal, or a combination of these.
Your RDBMS system may provide other column functions, such as standard deviation.

Scalar functions perform an operation on a column value of each SELECTed row and returns a value for each row. Scalar functions can be used in a SELECT, WHERE, or HAVING clause and will be discussed later.