Question 9

Explanation

Let's examine each statement about group functions (also called aggregate functions) like SUM, AVG, MAX, MIN, COUNT, etc., and determine which are true.

True Statements:

1. They can be used on columns or expressions.

• True.
  Group functions can operate on:

  • Columns: SUM(salary)

  • Expressions: SUM(salary + bonus)

2. They can be passed as an argument to another group function.

• True.
  This is known as a nested group function (though rarely used).
  Example:

  SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id;

  (Here, AVG(salary) is grouped by department, then MAX is applied to those averages.)

3. They can be used with a SQL statement that has the GROUP BY clause.

• True.
  In fact, group functions are commonly used with GROUP BY to get aggregated results per group.
  Example:

  SELECT department_id, SUM(salary)
  FROM employees
  GROUP BY department_id;

4. They can be used along with the single-row function in the SELECT clause of a SQL statement.

• True.
  Single-row functions and group functions can be used together in the SELECT clause.
  Example:

  SELECT UPPER(department_name), AVG(salary)
  FROM employees
  JOIN departments USING (department_id)
  GROUP BY department_name;

False Statement:

They can be used on only one column in the SELECT clause of a SQL statement.

• False.
  You can use multiple group functions in the same SELECT clause:

  SELECT AVG(salary), MAX(salary), MIN(salary)
  FROM employees;

Oracle group operations. The features include aggregate functions, the GROUP BY clause, the HAVING clause, and the extensions to the GROUP BY clause—ROLLUP, CUBE, and GROUPING SETS.

Aggregate Functions

An aggregate function summarizes the results of an expression over a number of rows, returning a single value.

√  They can be used on columns or expressions.

Syntax

aggregate_function([DISTINCT | ALL] expression)

Parameters or Arguments

aggregate_function - name of the function—e.g., SUM, COUNT, AVG, MAX, MIN

DISTINCT - Specifies that the aggregate function should consider only distinct values of the argument expression.

ALL - Specifies that the aggregate function should consider all values, including all duplicate values, of the argument expression. The default is ALL.

expression - Specifies a column, or any other expression, on which you want to perform the aggregation.

NULLs and Aggregate Functions

All aggregate functions ignore NULLs other than COUNT(*) and GROUP BY.  For example, COUNT(column_name) ignores NULLs, whereas COUNT(*) doesn’t. The reason COUNT(*) doesn’t ignore NULLs is because it counts rows, not column values. The concept of NULL doesn’t apply to a row as a whole.  SUM, MAX, MIN, AVG, etc., all ignore NULLs.

Use of DISTINCT and ALL

Most aggregate functions allow the use of DISTINCT or ALL along with the expression argument. DISTINCT allows you to disregard duplicate expression values, while ALL causes duplicate expression values to be included in the result.

The aggregate functions that take more than one argument as input don’t allow the use of DISTINCT. These include CORR, COVAR_POP, COVAR_SAMP, and all the linear regression functions.

Functions that take only one argument as input don’t allow the use of DISTINCT. This category includes STTDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP, and GROUPING.

The GROUP BY Clause

The GROUP BY clause, along with the aggregate functions, groups a result set into multiple groups, and then produces a single row of summary information for each group.

• Generally speaking, any nonaggregate expression in your SELECT clause must also be reflected in your GROUP BY clause.

• Aggregate expressions generally require a GROUP BY clause.

• GROUP BY clause must include all nonaggregate expressions

• Aggregate functions not allowed in GROUP BY clause.

• Constants can be omitted from the GROUP BY clause.

√ They can be passed as an argument to another group function.

√ They can be used with a SQL statement that has the GROUP BY clause.

Single-row function in the SELECT clause

Examples of proper usage.

SELECT col1, col2, col3, MAX(col4)
...
GROUP BY col1, col2, col3

SELECT col1, col2, col3, MAX(col4)
...
GROUP BY col1, col2, col3, col5, col6

Examples of improper usage which will render an error.

SELECT col1, col2, col3, MAX(col4)
...
GROUP BY col1, col2

√  They can be used along with the single-row function in the SELECT clause of a SQL statement.