iAnywhere.com - NULL values and subtotal rows

NULL values and subtotal rows

When rows in the input to a GROUP BY operation contain NULL, there is the possibility of confusion between subtotal rows added by the ROLLUP, CUBE, or GROUPING SETS operations and rows that contain NULL values that are part of the original input data.

The GROUPING function distinguishes subtotal rows from others by taking a column in the group by list as its argument, and returning 1 if the column is NULL because the row is a subtotal row, and 0 otherwise.

Example

The following example includes GROUPING columns in the result set. Rows are highlighted that contain NULL as a result of the input data, not because they are subtotal rows. The GROUPING columns are highlighted. The query is an outer join between the employee table and the sales_order table. The query selects female employees who live in either Texas, New York, or California. NULL appears in the columns corresponding to those female employees who are not sales representatives (and therefore have no sales).

SELECT employee.emp_id AS Employee,
       year(order_date) AS Year,
       COUNT(*) AS Orders,
       GROUPING ( Employee ) AS GE,
       GROUPING ( Year ) AS GY
FROM employee LEFT OUTER JOIN sales_order
ON employee.emp_id = sales_order.sales_rep
WHERE employee.sex IN ('F') 
AND employee.state IN ('TX', 'CA', 'NY')
GROUP BY ROLLUP (Year, Employee)
ORDER BY Year, Employee

The table that follows represents the result set from the query.

A result set from a ROLLUP query where the NULL values that are a product of the result set, not subtotalled rows, are highlighted.