>>-select-clause--from-clause--+--------------+-----------------> '-where-clause-' >--+-----------------+--+---------------+-----------------------> '-group-by-clause-' '-having-clause-' >--+-----------------+--+--------------------+----------------->< '-order-by-clause-' '-fetch-first-clause-'
The subselect is a component of the fullselect.
A subselect specifies a result table derived from the tables, views or nicknames identified in the FROM clause. The derivation can be described as a sequence of operations in which the result of each operation is input for the next. (This is only a way of describing the subselect. The method used to perform the derivation may be quite different from this description.)
The clauses of the subselect are processed in the following sequence:
A subselect that contains an ORDER BY or FETCH FIRST clause cannot be specified:
For example, the following is not valid (SQLSTATE 428FJ):
SELECT * FROM T1 ORDER BY C1 UNION SELECT * FROM T2 ORDER BY C1
The following example is valid:
(SELECT * FROM T1 ORDER BY C1) UNION (SELECT * FROM T2 ORDER BY C1)
The SELECT clause specifies the columns of the final result table. The column values are produced by the application of the select list to R. The select list is the names or expressions specified in the SELECT clause, and R is the result of the previous operation of the subselect. For example, if the only clauses specified are SELECT, FROM, and WHERE, R is the result of that WHERE clause.
Two rows are duplicates of one another only if each value in the first is equal to the corresponding value of the second. For determining duplicates, two null values are considered equal.
The list of names is established when the program containing the SELECT clause is bound. Hence * (the asterisk) does not identify any columns that have been added to a table after the statement containing the table reference has been bound.
The list of names is established when the statement containing the SELECT clause is bound. Therefore, * does not identify any columns that have been added to a table after the statement has been bound.
The number of columns in the result of SELECT is the same as the number of expressions in the operational form of the select list (that is, the list established when the statement is prepared), and cannot exceed 500 for a 4K page size or 1012 for an 8K, 16K, or 32K page size.
For limitations on the select list, see "Restrictions Using Varying-Length Character Strings".
Some of the results of applying the select list to R depend on whether or not GROUP BY or HAVING is used. The results are described in two separate lists:
In either case the nth column of the result contains the values specified by applying the nth expression in the operational form of the select list.
Result columns do not allow null values if they are derived from:
Result columns allow null values if they are derived from:
Each column of the result of SELECT acquires a data type from the expression from which it is derived.
| When the expression is ... | The data type of the result column is ... |
|---|---|
| the name of any numeric column | the same as the data type of the column, with the same precision and scale for DECIMAL columns. |
| an integer constant | INTEGER. |
| a decimal constant | DECIMAL, with the precision and scale of the constant. |
| a floating-point constant | DOUBLE. |
| the name of any numeric variable | the same as the data type of the variable, with the same precision and scale for DECIMAL variables. |
| a hexadecimal constant representing n bytes | VARCHAR(n); the code page is the database code page. |
| the name of any string column | the same as the data type of the column, with the same length attribute. |
| the name of any string variable | the same as the data type of the variable, with the same length attribute; if the data type of the variable is not identical to an SQL data type (for example, a NUL-terminated string in C), the result column is a varying-length string. |
| a character string constant of length n | VARCHAR(n). |
| a graphic string constant of length n | VARGRAPHIC(n). |
| the name of a datetime column | the same as the data type of the column. |
| the name of a user-defined type column | the same as the data type of the column. |
| the name of a reference type column | the same as the data type of the column. |
The FROM clause specifies an intermediate result table.
If one table-reference is specified, the intermediate result table is simply the result of that table-reference. If more than one table-reference is specified, the intermediate result table consists of all possible combinations of the rows of the specified table-references (the Cartesian product). Each row of the result is a row from the first table-reference concatenated with a row from the second table-reference, concatenated in turn with a row from the third, and so on. The number of rows in the result is the product of the number of rows in all the individual table-references. For a description of table-reference, see table-reference.
Each table-name, view-name or nickname specified as a table-reference must identify an existing table, view or nickname at the application server or the table-name of a common table expression defined preceding the fullselect containing the table-reference. If the table-name references a typed table, the name denotes the UNION ALL of the table with all its subtables, with only the columns of the table-name. Similarly, if the view-name references a typed view, the name denotes the UNION ALL of the view with all its subviews, with only the columns of the view-name.
The use of ONLY(table-name) or ONLY(view-name) means that the rows of the proper subtables or subviews are not included. If the table-name used with ONLY does not have subtables, then ONLY(table-name) is equivalent to specifying table-name. If the view-name used with ONLY does not have subviews, then ONLY(view-name) is equivalent to specifying view-name.
The use of OUTER(table-name) or OUTER(view-name) represents a virtual table. If the table-name or view-name used with OUTER does not have subtables or subviews, then specifying OUTER is equivalent to not specifying OUTER. OUTER(table-name) is derived from table-name as follows:
The previous points also apply to OUTER(view-name), substituting view-name for table-name and subview for subtable.
The use of ONLY or OUTER requires the SELECT privilege on every subtable of table-name or subview of view-name.
Each function-name together with the types of its arguments, specified as a table reference must resolve to an existing table function at the application server.
A fullselect in parentheses followed by a correlation name is called a nested table expression.
A joined-table specifies an intermediate result set that is the result of one or more join operations. For more information, see joined-table.
The exposed names of all table references should be unique. An exposed name is:
Each correlation-name is defined as a designator of the immediately preceding table-name, view-name, nickname, function-name reference or nested table expression. Any qualified reference to a column for a table, view, table function or nested table expression must use the exposed name. If the same table name, view or nickname name is specified twice, at least one specification should be followed by a correlation-name. The correlation-name is used to qualify references to the columns of the table, view or nickname. When a correlation-name is specified, column-names can also be specified to give names to the columns of the table-name, view-name, nickname, function-name reference or nested table expression.
In general, table functions and nested table expressions can be specified on any from-clause. Columns from the table functions and nested table expressions can be referenced in the select list and in the rest of the subselect using the correlation name which must be specified. The scope of this correlation name is the same as correlation names for other table, view or nickname in the FROM clause. A nested table expression can be used:
4 An expression in the select list of a nested table 4 expression that is referenced within, or is the target of, a data 4 change statement within a fullselect is only valid when it does not 4 include: 4
44 If a view is referenced directly in, or as the target of 4 a nested table expression in a data change statement within a FROM 4 clause, the view must either be symmetric (have WITH CHECK OPTION 4 specified) or satisfy the restriction for a WITH CHECK OPTION view.
4 If the target of a data change statement within a FROM 4 clause is a nested table expression, the modified rows are not 4 requalified, WHERE clause predicates are not re-evaluated, and ORDER 4 BY or FETCH FIRST operations are not redone.
2 The optional tablesample-clause can be used to obtain 2 a random subset (a sample) of the rows from the specified 2 table-name, rather than the entire contents of that 2 table-name, for this query. 2 This sampling is in addition to any predicates that are specified in 2 the where-clause. 2 Unless the optional REPEATABLE clause is specified, each 2 execution of the query will usually yield a different sample, 2 except in degenerate cases where the table is so small relative to 2 the sample size that any sample must return the same rows. 2 The size of the sample is controlled by the 2 numeric-expression1 in parentheses, representing an 2 approximate percentage (P) of the table to be returned. 2 The method by which the sample is obtained is specified after the 2 TABLESAMPLE keyword, and can be either BERNOULLI or SYSTEM. 2 For both methods, the exact number of rows in the sample may be 2 different for each execution of the query, but on average should be 2 approximately P percent of the table, before any predicates further 2 reduce the number of rows.
2 The table-name must be a stored table. 2 It can be a materialized query table (MQT) name, but not a 2 subselect or table expression for which an MQT has been defined, 2 because there is no guarantee that the database manager will route 2 to the MQT for that subselect.
2 Semantically, sampling of a table occurs before any 2 other query processing, such as applying predicates or performing 2 joins. 2 Repeated accesses of a sampled table within a single execution of 2 a query (such as in a nested-loop join or a correlated subquery) 2 will return the same sample. 2 More than one table may be sampled in a query.
2 BERNOULLI sampling considers each row individually. 2 It includes each row in the sample with probability P/100 (where P 2 is the value of numeric-expression1), and excludes each 2 row with probability 1 - P/100, independently of the other rows. 2 So if the numeric-expression1 evaluated to the value 10, 2 representing a ten percent sample, each row would be included with 2 probability 0.1, and excluded with probability 0.9.
2 SYSTEM sampling permits the database manager to 2 determine the most efficient manner in which to perform the 2 sampling. 2 In most cases, SYSTEM sampling applied to a table-name 2 means that each page of table-name is included in the 2 sample with probability P/100, and excluded with probability 1 - P/100. 2 All rows on each page that is included qualify for the sample. 2 SYSTEM sampling of a table-name generally executes much 2 faster than BERNOULLI sampling, because fewer data pages need to be 2 retrieved; however, it can often yield less accurate estimates for 2 aggregate functions (SUM(SALES), for example), especially if the 2 rows of table-name are clustered on any columns referenced 2 in that query. 2 The optimizer may in certain circumstances decide that it is more 2 efficient to perform SYSTEM sampling as if it were BERNOULLI 2 sampling, for example when a predicate on table-name can 2 be applied by an index and is much more selective than the sampling 2 rate P.
2 The numeric-expression1 specifies the size of 2 the sample to be obtained from table-name, expressed as a 2 percentage. 2 It must be a constant numeric expression that cannot contain 2 columns, parameter markers, or host variables. 2 The expression must evaluate to a positive number that is less than 2 or equal to 100, but can be between 1 and 0. 2 For example, a value of 0.01 represents one one-hundredth of a 2 percent, meaning that 1 row in 10 000 would be sampled, 2 on average. 2 A numeric-expression1 that evaluates to 100 is handled as 2 if the tablesample-clause were not specified. 2 If numeric-expression1 evaluates to the null value, or 2 to a value that is greater than 100 or less than 0, an error is 2 returned (SQLSTATE 2202H).
2 It is sometimes desirable for sampling to be 2 repeatable from one execution of the query to the next; for 2 example, during regression testing or query "debugging". 2 This can be accomplished by specifying the REPEATABLE clause. 2 The REPEATABLE clause requires the specification of a 2 numeric-expression2 in parentheses, which serves the same 2 role as the seed in a random number generator. 2 Adding the REPEATABLE clause to the tablesample-clause of any 2 table-name ensures that repeated executions of that query 2 (using the same value for numeric-expression2) return the 2 same sample, assuming, of course, that the data itself has not been 2 updated, reorganized, or repartitioned. 2 To guarantee that the same sample of table-name is used 2 across multiple queries, use of a global temporary table is 2 recommended. 2 Alternatively, the multiple queries could be combined into one 2 query, with multiple references to a sample that is defined using 2 the WITH clause.
2 Following are some examples:
2 Example 1: 2 Request a 10% Bernoulli sample of the Sales table for auditing 2 purposes. 2
2SELECT * FROM Sales 2 TABLESAMPLE BERNOULLI(10)
2 Example 2: 2 Compute the total sales revenue in the Northeast region for each 2 product category, using a random 1% SYSTEM sample of the Sales table. 2 The semantics of SUM are for the sample itself, so to extrapolate the 2 sales to the entire Sales table, the query must divide that SUM by 2 the sampling rate (0.01). 2
2SELECT SUM(Sales.Revenue) / (0.01) 2 FROM Sales TABLESAMPLE SYSTEM(1) 2 WHERE Sales.RegionName = 'Northeast' 2 GROUP BY Sales.ProductCategory
2 Example 3: 2 Using the REPEATABLE clause, modify the previous query to ensure that 2 the same (yet random) result is obtained each time the query is 2 executed. 2 (The value of the constant enclosed by parentheses is arbitrary.) 2
2SELECT SUM(Sales.Revenue) / (0.01) 2 FROM Sales TABLESAMPLE SYSTEM(1) REPEATABLE(3578231) 2 WHERE Sales.RegionName = 'Northeast' 2 GROUP BY Sales.ProductCategory
In general, a table function, together with its argument values, can be referenced in the FROM clause of a SELECT in exactly the same way as a table or view. There are, however, some special considerations which apply.
Unless alternate column names are provided following the correlation-name, the column names for the table function are those specified in the RETURNS clause of the CREATE FUNCTION statement. This is analogous to the names of the columns of a table, which are defined in the CREATE TABLE statement.
The arguments specified in a table function reference, together with the function name, are used by an algorithm called function resolution to determine the exact function to be used. This is no different from what happens with other functions (such as scalar functions) that are used in a statement.
As with scalar function arguments, table function arguments can in general be any valid SQL expression. The following examples are valid syntax:
Example 1: SELECT c1 FROM TABLE( tf1('Zachary') ) AS z WHERE c2 = 'FLORIDA'; Example 2: SELECT c1 FROM TABLE( tf2 (:hostvar1, CURRENT DATE) ) AS z; Example 3: SELECT c1 FROM t WHERE c2 IN (SELECT c3 FROM TABLE( tf5(t.c4) ) AS z -- correlated reference ) -- to previous FROM clause
Table functions that are specified with the MODIFIES SQL DATA 4 option can only be used as the last table reference in a 4 select-statement, common-table-expression, or 4 RETURN statement that is a subselect, a SELECT INTO, or a 4 row-fullselect in a SET statement. 4 Only one table function is allowed in one FROM clause, and the table 4 function arguments must be correlated to all other table references 4 in the subselect (SQLSTATE 429BL). 4 The following examples have valid syntax for a table function with 4 the MODIFIES SQL DATA property: 4
4Example 1: SELECT c1 4 FROM TABLE( tfmod('Jones') ) AS z 4 4 Example 2: SELECT c1 4 FROM t1, t2, TABLE( tfmod(t1.c1, t2.c1) ) AS z 4 4 Example 3: SET var = 4 (SELECT c1 4 FROM TABLE( tfmod('Jones') ) AS z 4 4 Example 4: RETURN SELECT c1 4 FROM TABLE( tfmod('Jones') ) AS z 4 4 Example 5: WITH v1(c1) AS 4 (SELECT c1 4 FROM TABLE( tfmod(:hostvar1) ) AS z) 4 SELECT c1 4 FROM v1, t1 WHERE v1.c1 = t1.c1
Correlated references can be used in nested table expressions or as arguments to table functions. The basic rule that applies for both these cases is that the correlated reference must be from a table-reference at a higher level in the hierarchy of subqueries. This hierarchy includes the table-references that have already been resolved in the left-to-right processing of the FROM clause. For nested table expressions, the TABLE keyword must appear before the fullselect. So the following examples are valid syntax:
Example 1: SELECT t.c1, z.c5 FROM t, TABLE( tf3(t.c2) ) AS z -- t precedes tf3 WHERE t.c3 = z.c4; -- in FROM, so t.c2 -- is known Example 2: SELECT t.c1, z.c5 FROM t, TABLE( tf4(2 * t.c2) ) AS z -- t precedes tf3 WHERE t.c3 = z.c4; -- in FROM, so t.c2 -- is known Example 3: SELECT d.deptno, d.deptname, empinfo.avgsal, empinfo.empcount FROM department d, TABLE (SELECT AVG(e.salary) AS avgsal, COUNT(*) AS empcount FROM employee e -- department precedes WHERE e.workdept=d.deptno -- and TABLE is ) AS empinfo; -- specified, so -- d.deptno is known
But the following examples are not valid:
Example 4: SELECT t.c1, z.c5 FROM TABLE( tf6(t.c2) ) AS z, t -- cannot resolve t in t.c2! WHERE t.c3 = z.c4; -- compare to Example 1 above. Example 5: SELECT a.c1, b.c5 FROM TABLE( tf7a(b.c2) ) AS a, TABLE( tf7b(a.c6) ) AS b WHERE a.c3 = b.c4; -- cannot resolve b in b.c2! Example 6: SELECT d.deptno, d.deptname, empinfo.avgsal, empinfo.empcount FROM department d, (SELECT AVG(e.salary) AS avgsal, COUNT(*) AS empcount FROM employee e -- department precedes WHERE e.workdept=d.deptno -- but TABLE is not ) AS empinfo; -- specified, so -- d.deptno is unknown4 4 4
A data-change-table-reference clause 4 specifies an intermediate result table. 4 This table is based on the rows that are directly changed by the 4 searched UPDATE, searched DELETE, or INSERT statement that is 4 included in the clause. 4 A data-change-table-reference can be specified as the only 4 table-reference in the FROM clause of the outer fullselect 4 that is used in a select-statement, a SELECT INTO 4 statement, or a common table expression. 4 A data-change-table-reference can be specified as the only 4 table reference in the only fullselect in a SET Variable statement 4 (SQLSTATE 428FL). 4 The target table or view of the data change statement is considered 4 to be a table or view that is referenced in the query; therefore, 4 the authorization ID of the query must have SELECT privilege on 4 that target table or view.
4The target of the UPDATE, DELETE, or INSERT statement cannot 4 be a temporary view defined in a common table expression (SQLSTATE 4 42807).
4The content of the intermediate result table for a 4 data-change-table-reference is determined when the cursor 4 opens. 4 The intermediate result table contains all manipulated rows, 4 including all the columns in the specified target table or view. 4 All the columns of the target table or view for an SQL data change 4 statement are accessible using the column names from the target table 4 or view. 4 If an INCLUDE clause was specified within a data change statement, 4 the intermediate result table will contain these additional columns.
A joined table specifies an intermediate result table that is the result of either an inner join or an outer join. The table is derived by applying one of the join operators: INNER, LEFT OUTER, RIGHT OUTER, or FULL OUTER to its operands.
Inner joins can be thought of as the cross product of the tables (combine each row of the left table with every row of the right table), keeping only the rows where the join condition is true. The result table may be missing rows from either or both of the joined tables. Outer joins include the inner join and preserve these missing rows. There are three types of outer joins:
If a join-operator is not specified, INNER is implicit. The order in which multiple joins are performed can affect the result. Joins can be nested within other joins. The order of processing for joins is generally from left to right, but based on the position of the required join-condition. Parentheses are recommended to make the order of nested joins more readable. For example:
TB1 LEFT JOIN TB2 ON TB1.C1=TB2.C1 RIGHT JOIN TB3 LEFT JOIN TB4 ON TB3.C1=TB4.C1 ON TB1.C1=TB3.C1
is the same as:
(TB1 LEFT JOIN TB2 ON TB1.C1=TB2.C1) RIGHT JOIN (TB3 LEFT JOIN TB4 ON TB3.C1=TB4.C1) ON TB1.C1=TB3.C1
A joined table can be used in any context in which any form of the SELECT statement is used. A view or a cursor is read-only if its SELECT statement includes a joined table.
A join-condition is a search-condition except that:
An error occurs if the join condition does not comply with these rules (SQLSTATE 42972).
Column references are resolved using the rules for resolution of column name qualifiers. The same rules that apply to predicates apply to join conditions.
A join-condition specifies pairings of T1 and T2, where T1 and T2 are the left and right operand tables of the JOIN operator of the join-condition. For all possible combinations of rows of T1 and T2, a row of T1 is paired with a row of T2 if the join-condition is true. When a row of T1 is joined with a row of T2, a row in the result consists of the values of that row of T1 concatenated with the values of that row of T2. The execution might involve the generation of a null row. The null row of a table consists of a null value for each column of the table, regardless of whether the columns allow null values.
The following summarizes the result of the join operations:
The WHERE clause specifies an intermediate result table that consists of those rows of R for which the search-condition is true. R is the result of the FROM clause of the subselect.
The search-condition must conform to the following rules:
Any subquery in the search-condition is effectively executed for each row of R, and the results are used in the application of the search-condition to the given row of R. A subquery is actually executed for each row of R only if it includes a correlated reference. In fact, a subquery with no correlated references may be executed just once, whereas a subquery with a correlated reference may have to be executed once for each row.
The GROUP BY clause specifies an intermediate result table that consists of a grouping of the rows of R. R is the result of the previous clause of the subselect.
In its simplest form, a GROUP BY clause contains a grouping expression. A grouping expression is an expression used in defining the grouping of R. Each column name included in grouping-expression must unambiguously identify a column of R (SQLSTATE 42702 or 42703). A grouping expression cannot include a scalar-fullselect (SQLSTATE 42822) or any function that is variant or has an external action (SQLSTATE 42845).
More complex forms of the GROUP BY clause include grouping-sets and super-groups. For a description of these forms, see grouping-sets and super-groups, respectively.
The result of GROUP BY is a set of groups of rows. Each row in this result represents the set of rows for which the grouping-expression is equal. For grouping, all null values from a grouping-expression are considered equal.
A grouping-expression can be used in a search condition in a HAVING clause, in an expression in a SELECT clause or in a sort-key-expression of an ORDER BY clause (see order-by-clause for details). In each case, the reference specifies only one value for each group. For example, if the grouping-expression is col1+col2, then an allowed expression in the select list would be col1+col2+3. Associativity rules for expressions would disallow the similar expression, 3+col1+col2, unless parentheses are used to ensure that the corresponding expression is evaluated in the same order. Thus, 3+(col1+col2) would also be allowed in the select list. If the concatenation operator is used, the grouping-expression must be used exactly as the expression was specified in the select list.
If the grouping-expression contains varying-length strings with trailing blanks, the values in the group can differ in the number of trailing blanks and may not all have the same length. In that case, a reference to the grouping-expression still specifies only one value for each group, but the value for a group is chosen arbitrarily from the available set of values. Thus, the actual length of the result value is unpredictable.
As noted, there are some cases where the GROUP BY clause cannot refer directly to a column that is specified in the SELECT clause as an expression (scalar-fullselect, variant or external action functions). To group using such an expression, use a nested table expression or a common table expression to first provide a result table with the expression as a column of the result. For an example using nested table expressions, see Example A9.
A grouping-sets specification allows multiple grouping clauses to be specified in a single statement. This can be thought of as the union of two or more groups of rows into a single result set. It is logically equivalent to the union of multiple subselects with the group by clause in each subselect corresponding to one grouping set. A grouping set can be a single element or can be a list of elements delimited by parentheses, where an element is either a grouping-expression or a super-group. Using grouping-sets allows the groups to be computed with a single pass over the base table.
The grouping-sets specification allows either a simple grouping-expression to be used, or the more complex forms of super-groups. For a description of super-groups, see super-groups.
Note that grouping sets are the fundamental building blocks for GROUP BY operations. A simple GROUP BY with a single column can be considered a grouping set with one element. For example:
GROUP BY a is the same as
GROUP BY GROUPING SETS((a))
and
GROUP BY a,b,c is the same as
GROUP BY GROUPING SETS((a,b,c))
Non-aggregation columns from the select list of the subselect that are excluded from a grouping set will return a null for such columns for each row generated for that grouping set. This reflects the fact that aggregation was done without considering the values for those columns.
Example C2 through Example C7 illustrate the use of grouping sets.
A ROLLUP grouping is a series of grouping-sets. The general specification of a ROLLUP with n elements
GROUP BY ROLLUP(C1,C2,...,Cn-1,Cn)
is equivalent to
GROUP BY GROUPING SETS((C1,C2,...,Cn-1,Cn) (C1,C2,...,Cn-1) ... (C1,C2) (C1) () )
Note that the n elements of the ROLLUP translate to n+1 grouping sets. Note also that the order in which the grouping-expressions is specified is significant for ROLLUP. For example:
GROUP BY ROLLUP(a,b)
is equivalent to
GROUP BY GROUPING SETS((a,b) (a) () )
while
GROUP BY ROLLUP(b,a)
is the same as
GROUP BY GROUPING SETS((b,a) (b) () )
The ORDER BY clause is the only way to guarantee the order of the rows in the result set. Example C3 illustrates the use of ROLLUP.
Like a ROLLUP, a CUBE grouping can also be thought of as a series of grouping-sets. In the case of a CUBE, all permutations of the cubed grouping-expression-list are computed along with the grand total. Therefore, the n elements of a CUBE translate to 2**n (2 to the power n) grouping-sets. For instance, a specification of
GROUP BY CUBE(a,b,c)
is equivalent to
GROUP BY GROUPING SETS((a,b,c) (a,b) (a,c) (b,c) (a) (b) (c) () )
Notice that the 3 elements of the CUBE translate to 8 grouping sets.
The order of specification of elements does not matter for CUBE. 'CUBE (DayOfYear, Sales_Person)' and 'CUBE (Sales_Person, DayOfYear)' yield the same result sets. The use of the word 'same' applies to content of the result set, not to its order. The ORDER BY clause is the only way to guarantee the order of the rows in the result set. Example C4 illustrates the use of CUBE.
The rules for a grouping-expression are described in group-by-clause. For example, suppose that a query is to return the total expenses for the ROLLUP of City within a Province but not within a County. However the clause:
GROUP BY ROLLUP(Province, County, City)
results in unwanted sub-total rows for the County. In the clause
GROUP BY ROLLUP(Province, (County, City))
the composite (County, City) forms one element in the ROLLUP and, therefore, a query that uses this clause will yield the desired result. In other words, the two element ROLLUP
GROUP BY ROLLUP(Province, (County, City))
generates
GROUP BY GROUPING SETS((Province, County, City) (Province) () )
while the 3 element ROLLUP would generate
GROUP BY GROUPING SETS((Province, County, City) (Province, County) (Province) () )
Example C2 also utilizes composite column values.
This can be used to combine any of the types of GROUP BY clauses. When simple grouping-expression fields are combined with other groups, they are "appended" to the beginning of the resulting grouping sets. When ROLLUP or CUBE expressions are combined, they operate like "multipliers" on the remaining expression, forming additional grouping set entries according to the definition of either ROLLUP or CUBE.
For instance, combining grouping-expression elements acts as follows:
GROUP BY a, ROLLUP(b,c)
is equivalent to
GROUP BY GROUPING SETS((a,b,c) (a,b) (a) )
Or similarly,
GROUP BY a, b, ROLLUP(c,d)
is equivalent to
GROUP BY GROUPING SETS((a,b,c,d) (a,b,c) (a,b) )
Combining of ROLLUP elements acts as follows:
GROUP BY ROLLUP(a), ROLLUP(b,c)
is equivalent to
GROUP BY GROUPING SETS((a,b,c) (a,b) (a) (b,c) (b) () )
Similarly,
GROUP BY ROLLUP(a), CUBE(b,c)
is equivalent to
GROUP BY GROUPING SETS((a,b,c) (a,b) (a,c) (a) (b,c) (b) (c) () )
Combining of CUBE and ROLLUP elements acts as follows:
GROUP BY CUBE(a,b), ROLLUP(c,d)
is equivalent to
GROUP BY GROUPING SETS((a,b,c,d) (a,b,c) (a,b) (a,c,d) (a,c) (a) (b,c,d) (b,c) (b) (c,d) (c) () )
Like a simple grouping-expression, combining grouping sets also eliminates duplicates within each grouping set. For instance,
GROUP BY a, ROLLUP(a,b)
is equivalent to
GROUP BY GROUPING SETS((a,b) (a) )
A more complete example of combining grouping sets is to construct a result set that eliminates certain rows that would be returned for a full CUBE aggregation.
For example, consider the following GROUP BY clause:
GROUP BY Region, ROLLUP(Sales_Person, WEEK(Sales_Date)), CUBE(YEAR(Sales_Date), MONTH (Sales_Date))
The column listed immediately to the right of GROUP BY is simply grouped, those within the parenthesis following ROLLUP are rolled up, and those within the parenthesis following CUBE are cubed. Thus, the above clause results in a cube of MONTH within YEAR which is then rolled up within WEEK within Sales_Person within the Region aggregation. It does not result in any grand total row or any cross-tabulation rows on Region, Sales_Person or WEEK(Sales_Date) so produces fewer rows than the clause:
GROUP BY ROLLUP (Region, Sales_Person, WEEK(Sales_Date),
YEAR(Sales_Date), MONTH(Sales_Date) )
The HAVING clause specifies an intermediate result table that consists of those groups of R for which the search-condition is true. R is the result of the previous clause of the subselect. If this clause is not GROUP BY, R is considered to be a single group with no grouping columns.
Each column-name in the search condition must do one of the following:
A group of R to which the search condition is applied supplies the argument for each column function in the search condition, except for any function whose argument is a correlated reference.
If the search condition contains a subquery, the subquery can be thought of as being executed each time the search condition is applied to a group of R, and the results used in applying the search condition. In actuality, the subquery is executed for each group only if it contains a correlated reference. For an illustration of the difference, see Example A6 and Example A7.
A correlated reference to a group of R must either identify a grouping column or be contained within a column function.
When HAVING is used without GROUP BY, the select list can only be a column name within a column function, a correlated column reference, a literal, or a special register.
The ORDER BY clause specifies an ordering of the rows of the result table. If a single sort specification (one sort-key with associated direction) is identified, the rows are ordered by the values of that sort specification. If more than one sort specification is identified, the rows are ordered by the values of the first identified sort specification, then by the values of the second identified sort specification, and so on. Each sort-key cannot have a data type of LONG VARCHAR, CLOB, LONG VARGRAPHIC, DBCLOB, BLOB, DATALINK, distinct type on any of these types, or structured type (SQLSTATE 42907).
A named column in the select list may be identified by a sort-key that is a simple-integer or a simple-column-name. An unnamed column in the select list must be identified by an simple-integer or, in some cases, by a sort-key-expression that matches the expression in the select list (see details of sort-key-expression). A column is unnamed if the AS clause is not specified and it is derived from a constant, an expression with operators, or a function.
Ordering is performed in accordance with comparison rules. The null value is higher than all other values. If the ORDER BY clause does not completely order the rows, rows with duplicate values of all identified columns are displayed in an arbitrary order.
The simple-column-name may also identify a column name of a table, view, or nested table identified in the FROM clause if the query is a subselect. An error occurs if the subselect:
Determining which column is used for ordering the result is described under "Column names in sort keys" below.
Any column-name within a sort-key-expression must conform to the rules described under "Column names in sort keys" below.
There are a number of special cases that further restrict the expressions that can be specified.
The sort-key-expression must match exactly with an expression in the select list of the subselect (scalar-fullselects are never matched).
The sort-key-expression can:
Note that this form is not allowed in a fullselect (other than the degenerative form of a fullselect). For example, the following is not valid:
(SELECT C1 FROM T1 ORDER BY C1) UNION SELECT C1 FROM T2 ORDER BY ORDER OF T1
The following example is valid:
SELECT C1 FROM
(SELECT C1 FROM T1
UNION
SELECT C1 FROM T2
ORDER BY C1 ) AS UTABLE
ORDER BY ORDER OF UTABLE
The query must be a subselect (SQLSTATE 42877). The column name must unambiguously identify a column of some table, view or nested table in the FROM clause of the subselect (SQLSTATE 42702). The value of the column is used to compute the value of the sort specification.
If the column name is identical to the name of more than one column of the result table, the column name must unambiguously identify a column of some table, view or nested table in the FROM clause of the ordering subselect (SQLSTATE 42702). If the column name is identical to one column, that column is used to compute the value of the sort specification. If the column name is not identical to a column of the result table, then it must unambiguously identify a column of some table, view or nested table in the FROM clause of the fullselect in the select-statement (SQLSTATE 42702).
The column name must not be identical to the name of more than one column of the result table (SQLSTATE 42702). The column name must be identical to exactly one column of the result table (SQLSTATE 42707), and this column is used to compute the value of the sort specification.
The fetch-first-clause sets a maximum number of rows that can be retrieved. It lets the database manager know that the application does not want to retrieve more than integer rows, regardless of how many rows there might be in the result table when this clause is not specified. An attempt to fetch beyond integer rows is handled the same way as normal end of data (SQLSTATE 02000). The value of integer must be a positive integer (not zero).
Limiting the result table to the first integer rows can improve performance. The database manager will cease processing the query once it has determined the first integer rows. If both the fetch-first-clause and the optimize-for-clause are specified, the lower of the integer values from these clauses is used to influence the communications buffer size. The values are considered independently for optimization purposes.
4 If the fullselect contains an SQL data change statement 4 in the FROM clause, all the rows are modified regardless of the 4 limit on the number of rows to fetch.
Example A1: Select all columns and rows from the EMPLOYEE table.
SELECT * FROM EMPLOYEE
Example A2: Join the EMP_ACT and EMPLOYEE tables, select all the columns from the EMP_ACT table and add the employee's surname (LASTNAME) from the EMPLOYEE table to each row of the result.
SELECT EMP_ACT.*, LASTNAME FROM EMP_ACT, EMPLOYEE WHERE EMP_ACT.EMPNO = EMPLOYEE.EMPNO
Example A3: Join the EMPLOYEE and DEPARTMENT tables, select the employee number (EMPNO), employee surname (LASTNAME), department number (WORKDEPT in the EMPLOYEE table and DEPTNO in the DEPARTMENT table) and department name (DEPTNAME) of all employees who were born (BIRTHDATE) earlier than 1930.
SELECT EMPNO, LASTNAME, WORKDEPT, DEPTNAME FROM EMPLOYEE, DEPARTMENT WHERE WORKDEPT = DEPTNO AND YEAR(BIRTHDATE) < 1930
Example A4: Select the job (JOB) and the minimum and maximum salaries (SALARY) for each group of rows with the same job code in the EMPLOYEE table, but only for groups with more than one row and with a maximum salary greater than or equal to 27000.
SELECT JOB, MIN(SALARY), MAX(SALARY) FROM EMPLOYEE GROUP BY JOB HAVING COUNT(*) > 1 AND MAX(SALARY) >= 27000
Example A5: Select all the rows of EMP_ACT table for employees (EMPNO) in department (WORKDEPT) 'E11'. (Employee department numbers are shown in the EMPLOYEE table.)
SELECT * FROM EMP_ACT WHERE EMPNO IN (SELECT EMPNO FROM EMPLOYEE WHERE WORKDEPT = 'E11')
Example A6: From the EMPLOYEE table, select the department number (WORKDEPT) and maximum departmental salary (SALARY) for all departments whose maximum salary is less than the average salary for all employees.
SELECT WORKDEPT, MAX(SALARY) FROM EMPLOYEE GROUP BY WORKDEPT HAVING MAX(SALARY) < (SELECT AVG(SALARY) FROM EMPLOYEE)
The subquery in the HAVING clause would only be executed once in this example.
Example A7: Using the EMPLOYEE table, select the department number (WORKDEPT) and maximum departmental salary (SALARY) for all departments whose maximum salary is less than the average salary in all other departments.
SELECT WORKDEPT, MAX(SALARY) FROM EMPLOYEE EMP_COR GROUP BY WORKDEPT HAVING MAX(SALARY) < (SELECT AVG(SALARY) FROM EMPLOYEE WHERE NOT WORKDEPT = EMP_COR.WORKDEPT)
In contrast to Example A6, the subquery in the HAVING clause would need to be executed for each group.
Example A8: Determine the employee number and salary of sales representatives along with the average salary and head count of their departments.
This query must first create a nested table expression (DINFO) in order to get the AVGSALARY and EMPCOUNT columns, as well as the DEPTNO column that is used in the WHERE clause.
SELECT THIS_EMP.EMPNO, THIS_EMP.SALARY, DINFO.AVGSALARY, DINFO.EMPCOUNT FROM EMPLOYEE THIS_EMP, (SELECT OTHERS.WORKDEPT AS DEPTNO, AVG(OTHERS.SALARY) AS AVGSALARY, COUNT(*) AS EMPCOUNT FROM EMPLOYEE OTHERS GROUP BY OTHERS.WORKDEPT ) AS DINFO WHERE THIS_EMP.JOB = 'SALESREP' AND THIS_EMP.WORKDEPT = DINFO.DEPTNO
Using a nested table expression for this case saves the overhead of creating the DINFO view as a regular view. During statement preparation, accessing the catalog for the view is avoided and, because of the context of the rest of the query, only the rows for the department of the sales representatives need to be considered by the view.
Example A9: Display the average education level and salary for 5 random groups of employees.
This query requires the use of a nested table expression to set a random value for each employee so that it can subsequently be used in the GROUP BY clause.
SELECT RANDID , AVG(EDLEVEL), AVG(SALARY) FROM ( SELECT EDLEVEL, SALARY, INTEGER(RAND()*5) AS RANDID FROM EMPLOYEE ) AS EMPRAND GROUP BY RANDID
Example A10: Query the EMP_ACT table and return those project numbers that have an employee whose salary is in the top 10 of all employees.
SELECT EMP_ACT.EMPNO,PROJNO FROM EMP_ACT WHERE EMP_ACT.EMPNO IN (SELECT EMPLOYEE.EMPNO FROM EMPLOYEE ORDER BY SALARY DESC FETCH FIRST 10 ROWS ONLY)
Example B1: This example illustrates the results of the various joins using tables J1 and J2. These tables contain rows as shown.
SELECT * FROM J1 W X --- ------ A 11 B 12 C 13 SELECT * FROM J2 Y Z --- ------ A 21 C 22 D 23
The following query does an inner join of J1 and J2 matching the first column of both tables.
SELECT * FROM J1 INNER JOIN J2 ON W=Y W X Y Z --- ------ --- ------ A 11 A 21 C 13 C 22
In this inner join example the row with column W='C' from J1 and the row with column Y='D' from J2 are not included in the result because they do not have a match in the other table. Note that the following alternative form of an inner join query produces the same result.
SELECT * FROM J1, J2 WHERE W=Y
The following left outer join will get back the missing row from J1 with nulls for the columns of J2. Every row from J1 is included.
SELECT * FROM J1 LEFT OUTER JOIN J2 ON W=Y W X Y Z --- ------ --- ------ A 11 A 21 B 12 - - C 13 C 22
The following right outer join will get back the missing row from J2 with nulls for the columns of J1. Every row from J2 is included.
SELECT * FROM J1 RIGHT OUTER JOIN J2 ON W=Y W X Y Z --- ------ --- ------ A 11 A 21 C 13 C 22 - - D 23
The following full outer join will get back the missing rows from both J1 and J2 with nulls where appropriate. Every row from both J1 and J2 is included.
SELECT * FROM J1 FULL OUTER JOIN J2 ON W=Y W X Y Z --- ------ --- ------ A 11 A 21 C 13 C 22 - - D 23 B 12 - -
Example B2: Using the tables J1 and J2 from the previous example, examine what happens when and additional predicate is added to the search condition.
SELECT * FROM J1 INNER JOIN J2 ON W=Y AND X=13 W X Y Z --- ------ --- ------ C 13 C 22
The additional condition caused the inner join to select only 1 row compared to the inner join in Example B1.
Notice what the impact of this is on the full outer join.
SELECT * FROM J1 FULL OUTER JOIN J2 ON W=Y AND X=13 W X Y Z --- ------ --- ------ - - A 21 C 13 C 22 - - D 23 A 11 - - B 12 - -
The result now has 5 rows (compared to 4 without the additional predicate) since there was only 1 row in the inner join and all rows of both tables must be returned.
The following query illustrates that placing the same additional predicate in WHERE clause has completely different results.
SELECT * FROM J1 FULL OUTER JOIN J2 ON W=Y WHERE X=13 W X Y Z --- ------ --- ------ C 13 C 22
The WHERE clause is applied after the intermediate result of the full outer join. This intermediate result would be the same as the result of the full outer join query in Example B1. The WHERE clause is applied to this intermediate result and eliminates all but the row that has X=13. Choosing the location of a predicate when performing outer joins can have significant impact on the results. Consider what happens if the predicate was X=12 instead of X=13. The following inner join returns no rows.
SELECT * FROM J1 INNER JOIN J2 ON W=Y AND X=12
Hence, the full outer join would return 6 rows, 3 from J1 with nulls for the columns of J2 and 3 from J2 with nulls for the columns of J1.
SELECT * FROM J1 FULL OUTER JOIN J2 ON W=Y AND X=12 W X Y Z --- ------ --- ------ - - A 21 - - C 22 - - D 23 A 11 - - B 12 - - C 13 - -
If the additional predicate is in the WHERE clause instead, 1 row is returned.
SELECT * FROM J1 FULL OUTER JOIN J2 ON W=Y WHERE X=12 W X Y Z --- ------ --- ------ B 12 - -
Example B3: List every department with the employee number and last name of the manager, including departments without a manager.
SELECT DEPTNO, DEPTNAME, EMPNO, LASTNAME FROM DEPARTMENT LEFT OUTER JOIN EMPLOYEE ON MGRNO = EMPNO
Example B4: List every employee number and last name with the employee number and last name of their manager, including employees without a manager.
SELECT E.EMPNO, E.LASTNAME, M.EMPNO, M.LASTNAME FROM EMPLOYEE E LEFT OUTER JOIN DEPARTMENT INNER JOIN EMPLOYEE M ON MGRNO = M.EMPNO ON E.WORKDEPT = DEPTNO
The inner join determines the last name for any manager identified in the DEPARTMENT table and the left outer join guarantees that each employee is listed even if a corresponding department is not found in DEPARTMENT.
The queries in Example C1 through Example C4 use a subset of the rows in the SALES tables based on the predicate 'WEEK(SALES_DATE) = 13'.
SELECT WEEK(SALES_DATE) AS WEEK, DAYOFWEEK(SALES_DATE) AS DAY_WEEK, SALES_PERSON, SALES AS UNITS_SOLD FROM SALES WHERE WEEK(SALES_DATE) = 13
which results in:
WEEK DAY_WEEK SALES_PERSON UNITS_SOLD
----------- ----------- --------------- -----------
13 6 LUCCHESSI 3
13 6 LUCCHESSI 1
13 6 LEE 2
13 6 LEE 2
13 6 LEE 3
13 6 LEE 5
13 6 GOUNOT 3
13 6 GOUNOT 1
13 6 GOUNOT 7
13 7 LUCCHESSI 1
13 7 LUCCHESSI 2
13 7 LUCCHESSI 1
13 7 LEE 7
13 7 LEE 3
13 7 LEE 7
13 7 LEE 4
13 7 GOUNOT 2
13 7 GOUNOT 18
13 7 GOUNOT 1
Example C1: Here is a query with a basic GROUP BY clause over 3 columns:
SELECT WEEK(SALES_DATE) AS WEEK, DAYOFWEEK(SALES_DATE) AS DAY_WEEK, SALES_PERSON, SUM(SALES) AS UNITS_SOLD FROM SALES WHERE WEEK(SALES_DATE) = 13 GROUP BY WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE), SALES_PERSON ORDER BY WEEK, DAY_WEEK, SALES_PERSON
This results in:
WEEK DAY_WEEK SALES_PERSON UNITS_SOLD
----------- ----------- --------------- -----------
13 6 GOUNOT 11
13 6 LEE 12
13 6 LUCCHESSI 4
13 7 GOUNOT 21
13 7 LEE 21
13 7 LUCCHESSI 4
Example C2: Produce the result based on two different grouping sets of rows from the SALES table.
SELECT WEEK(SALES_DATE) AS WEEK, DAYOFWEEK(SALES_DATE) AS DAY_WEEK, SALES_PERSON, SUM(SALES) AS UNITS_SOLD FROM SALES WHERE WEEK(SALES_DATE) = 13 GROUP BY GROUPING SETS ( (WEEK(SALES_DATE), SALES_PERSON), (DAYOFWEEK(SALES_DATE), SALES_PERSON)) ORDER BY WEEK, DAY_WEEK, SALES_PERSON
This results in:
WEEK DAY_WEEK SALES_PERSON UNITS_SOLD
----------- ----------- --------------- -----------
13 - GOUNOT 32
13 - LEE 33
13 - LUCCHESSI 8
- 6 GOUNOT 11
- 6 LEE 12
- 6 LUCCHESSI 4
- 7 GOUNOT 21
- 7 LEE 21
- 7 LUCCHESSI 4
The rows with WEEK 13 are from the first grouping set and the other rows are from the second grouping set.
Example C3: If you use the 3 distinct columns involved in the grouping sets of Example C2 and perform a ROLLUP, you can see grouping sets for (WEEK,DAY_WEEK,SALES_PERSON), (WEEK, DAY_WEEK), (WEEK) and grand total.
SELECT WEEK(SALES_DATE) AS WEEK, DAYOFWEEK(SALES_DATE) AS DAY_WEEK, SALES_PERSON, SUM(SALES) AS UNITS_SOLD FROM SALES WHERE WEEK(SALES_DATE) = 13 GROUP BY ROLLUP ( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE), SALES_PERSON ) ORDER BY WEEK, DAY_WEEK, SALES_PERSON
This results in:
WEEK DAY_WEEK SALES_PERSON UNITS_SOLD
----------- ----------- --------------- -----------
13 6 GOUNOT 11
13 6 LEE 12
13 6 LUCCHESSI 4
13 6 - 27
13 7 GOUNOT 21
13 7 LEE 21
13 7 LUCCHESSI 4
13 7 - 46
13 - - 73
- - - 73
Example C4: If you run the same query as Example C3 only replace ROLLUP with CUBE, you can see additional grouping sets for (WEEK,SALES_PERSON), (DAY_WEEK,SALES_PERSON), (DAY_WEEK), (SALES_PERSON) in the result.
SELECT WEEK(SALES_DATE) AS WEEK, DAYOFWEEK(SALES_DATE) AS DAY_WEEK, SALES_PERSON, SUM(SALES) AS UNITS_SOLD FROM SALES WHERE WEEK(SALES_DATE) = 13 GROUP BY CUBE ( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE), SALES_PERSON ) ORDER BY WEEK, DAY_WEEK, SALES_PERSON
This results in:
WEEK DAY_WEEK SALES_PERSON UNITS_SOLD
----------- ----------- --------------- -----------
13 6 GOUNOT 11
13 6 LEE 12
13 6 LUCCHESSI 4
13 6 - 27
13 7 GOUNOT 21
13 7 LEE 21
13 7 LUCCHESSI 4
13 7 - 46
13 - GOUNOT 32
13 - LEE 33
13 - LUCCHESSI 8
13 - - 73
- 6 GOUNOT 11
- 6 LEE 12
- 6 LUCCHESSI 4
- 6 - 27
- 7 GOUNOT 21
- 7 LEE 21
- 7 LUCCHESSI 4
- 7 - 46
- - GOUNOT 32
- - LEE 33
- - LUCCHESSI 8
- - - 73
Example C5: Obtain a result set which includes a grand-total of selected rows from the SALES table together with a group of rows aggregated by SALES_PERSON and MONTH.
SELECT SALES_PERSON, MONTH(SALES_DATE) AS MONTH, SUM(SALES) AS UNITS_SOLD FROM SALES GROUP BY GROUPING SETS ( (SALES_PERSON, MONTH(SALES_DATE)), () ) ORDER BY SALES_PERSON, MONTH
This results in:
SALES_PERSON MONTH UNITS_SOLD --------------- ----------- ----------- GOUNOT 3 35 GOUNOT 4 14 GOUNOT 12 1 LEE 3 60 LEE 4 25 LEE 12 6 LUCCHESSI 3 9 LUCCHESSI 4 4 LUCCHESSI 12 1 - - 155
Example C6: This example shows two simple ROLLUP queries followed by a query which treats the two ROLLUPs as grouping sets in a single result set and specifies row ordering for each column involved in the grouping sets.
Example C6-1:
SELECT WEEK(SALES_DATE) AS WEEK, DAYOFWEEK(SALES_DATE) AS DAY_WEEK, SUM(SALES) AS UNITS_SOLD FROM SALES GROUP BY ROLLUP ( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE) ) ORDER BY WEEK, DAY_WEEK
results in:
WEEK DAY_WEEK UNITS_SOLD
----------- ----------- -----------
13 6 27
13 7 46
13 - 73
14 1 31
14 2 43
14 - 74
53 1 8
53 - 8
- - 155
Example C6-2:
SELECT MONTH(SALES_DATE) AS MONTH, REGION, SUM(SALES) AS UNITS_SOLD FROM SALES GROUP BY ROLLUP ( MONTH(SALES_DATE), REGION ); ORDER BY MONTH, REGION
results in:
MONTH REGION UNITS_SOLD
----------- --------------- -----------
3 Manitoba 22
3 Ontario-North 8
3 Ontario-South 34
3 Quebec 40
3 - 104
4 Manitoba 17
4 Ontario-North 1
4 Ontario-South 14
4 Quebec 11
4 - 43
12 Manitoba 2
12 Ontario-South 4
12 Quebec 2
12 - 8
- - 155
Example C6-3:
SELECT WEEK(SALES_DATE) AS WEEK, DAYOFWEEK(SALES_DATE) AS DAY_WEEK, MONTH(SALES_DATE) AS MONTH, REGION, SUM(SALES) AS UNITS_SOLD FROM SALES GROUP BY GROUPING SETS ( ROLLUP( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE) ), ROLLUP( MONTH(SALES_DATE), REGION ) ) ORDER BY WEEK, DAY_WEEK, MONTH, REGION
results in:
WEEK DAY_WEEK MONTH REGION UNITS_SOLD
----------- ----------- ----------- --------------- -----------
13 6 - - 27
13 7 - - 46
13 - - - 73
14 1 - - 31
14 2 - - 43
14 - - - 74
53 1 - - 8
53 - - - 8
- - 3 Manitoba 22
- - 3 Ontario-North 8
- - 3 Ontario-South 34
- - 3 Quebec 40
- - 3 - 104
- - 4 Manitoba 17
- - 4 Ontario-North 1
- - 4 Ontario-South 14
- - 4 Quebec 11
- - 4 - 43
- - 12 Manitoba 2
- - 12 Ontario-South 4
- - 12 Quebec 2
- - 12 - 8
- - - - 155
- - - - 155
Using the two ROLLUPs as grouping sets causes the result to include duplicate rows. There are even two grand total rows.
Observe how the use of ORDER BY has affected the results:
Example C7: In queries that perform multiple ROLLUPs in a single pass (such as Example C6-3) you may want to be able to indicate which grouping set produced each row. The following steps demonstrate how to provide a column (called GROUP) which indicates the origin of each row in the result set. By origin, we mean which one of the two grouping sets produced the row in the result set.
Step 1: Introduce a way of "generating" new data values, using a query which selects from a VALUES clause (which is an alternate form of a fullselect). This query shows how a table can be derived called "X" having 2 columns "R1" and "R2" and 1 row of data.
SELECT R1,R2 FROM (VALUES('GROUP 1','GROUP 2')) AS X(R1,R2);
results in:
R1 R2 ------- ------- GROUP 1 GROUP 2
Step 2: Form the cross product of this table "X" with the SALES table. This add columns "R1" and "R2" to every row.
SELECT R1, R2, WEEK(SALES_DATE) AS WEEK, DAYOFWEEK(SALES_DATE) AS DAY_WEEK, MONTH(SALES_DATE) AS MONTH, REGION, SALES AS UNITS_SOLD FROM SALES,(VALUES('GROUP 1','GROUP 2')) AS X(R1,R2)
This add columns "R1" and "R2" to every row.
Step 3: Now we can combine these columns with the grouping sets to include these columns in the rollup analysis.
SELECT R1, R2, WEEK(SALES_DATE) AS WEEK, DAYOFWEEK(SALES_DATE) AS DAY_WEEK, MONTH(SALES_DATE) AS MONTH, REGION, SUM(SALES) AS UNITS_SOLD FROM SALES,(VALUES('GROUP 1','GROUP 2')) AS X(R1,R2) GROUP BY GROUPING SETS ((R1, ROLLUP(WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE))), (R2,ROLLUP( MONTH(SALES_DATE), REGION ) ) ) ORDER BY WEEK, DAY_WEEK, MONTH, REGION
results in:
R1 R2 WEEK DAY_WEEK MONTH REGION UNITS_SOLD ------- ------- -------- --------- --------- --------------- ----------- GROUP 1 - 13 6 - - 27 GROUP 1 - 13 7 - - 46 GROUP 1 - 13 - - - 73 GROUP 1 - 14 1 - - 31 GROUP 1 - 14 2 - - 43 GROUP 1 - 14 - - - 74 GROUP 1 - 53 1 - - 8 GROUP 1 - 53 - - - 8 - GROUP 2 - - 3 Manitoba 22 - GROUP 2 - - 3 Ontario-North 8 - GROUP 2 - - 3 Ontario-South 34 - GROUP 2 - - 3 Quebec 40 - GROUP 2 - - 3 - 104 - GROUP 2 - - 4 Manitoba 17 - GROUP 2 - - 4 Ontario-North 1 - GROUP 2 - - 4 Ontario-South 14 - GROUP 2 - - 4 Quebec 11 - GROUP 2 - - 4 - 43 - GROUP 2 - - 12 Manitoba 2 - GROUP 2 - - 12 Ontario-South 4 - GROUP 2 - - 12 Quebec 2 - GROUP 2 - - 12 - 8 - GROUP 2 - - - - 155 GROUP 1 - - - - - 155
Step 4: Notice that because R1 and R2 are used in different grouping sets, whenever R1 is non-null in the result, R2 is null and whenever R2 is non-null in the result, R1 is null. That means you can consolidate these columns into a single column using the COALESCE function. You can also use this column in the ORDER BY clause to keep the results of the two grouping sets together.
SELECT COALESCE(R1,R2) AS GROUP, WEEK(SALES_DATE) AS WEEK, DAYOFWEEK(SALES_DATE) AS DAY_WEEK, MONTH(SALES_DATE) AS MONTH, REGION, SUM(SALES) AS UNITS_SOLD FROM SALES,(VALUES('GROUP 1','GROUP 2')) AS X(R1,R2) GROUP BY GROUPING SETS ((R1, ROLLUP(WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE))), (R2,ROLLUP( MONTH(SALES_DATE), REGION ) ) ) ORDER BY GROUP, WEEK, DAY_WEEK, MONTH, REGION;
results in:
GROUP WEEK DAY_WEEK MONTH REGION UNITS_SOLD ------- ----------- ----------- ----------- --------------- ----------- GROUP 1 13 6 - - 27 GROUP 1 13 7 - - 46 GROUP 1 13 - - - 73 GROUP 1 14 1 - - 31 GROUP 1 14 2 - - 43 GROUP 1 14 - - - 74 GROUP 1 53 1 - - 8 GROUP 1 53 - - - 8 GROUP 1 - - - - 155 GROUP 2 - - 3 Manitoba 22 GROUP 2 - - 3 Ontario-North 8 GROUP 2 - - 3 Ontario-South 34 GROUP 2 - - 3 Quebec 40 GROUP 2 - - 3 - 104 GROUP 2 - - 4 Manitoba 17 GROUP 2 - - 4 Ontario-North 1 GROUP 2 - - 4 Ontario-South 14 GROUP 2 - - 4 Quebec 11 GROUP 2 - - 4 - 43 GROUP 2 - - 12 Manitoba 2 GROUP 2 - - 12 Ontario-South 4 GROUP 2 - - 12 Quebec 2 GROUP 2 - - 12 - 8 GROUP 2 - - - - 155
Example C8: The following example illustrates the use of various column functions when performing a CUBE. The example also makes use of cast functions and rounding to produce a decimal result with reasonable precision and scale.
SELECT MONTH(SALES_DATE) AS MONTH, REGION, SUM(SALES) AS UNITS_SOLD, MAX(SALES) AS BEST_SALE, CAST(ROUND(AVG(DECIMAL(SALES)),2) AS DECIMAL(5,2)) AS AVG_UNITS_SOLD FROM SALES GROUP BY CUBE(MONTH(SALES_DATE),REGION) ORDER BY MONTH, REGION
This results in:
MONTH REGION UNITS_SOLD BEST_SALE AVG_UNITS_SOLD
----------- --------------- ----------- ----------- --------------
3 Manitoba 22 7 3.14
3 Ontario-North 8 3 2.67
3 Ontario-South 34 14 4.25
3 Quebec 40 18 5.00
3 - 104 18 4.00
4 Manitoba 17 9 5.67
4 Ontario-North 1 1 1.00
4 Ontario-South 14 8 4.67
4 Quebec 11 8 5.50
4 - 43 9 4.78
12 Manitoba 2 2 2.00
12 Ontario-South 4 3 2.00
12 Quebec 2 1 1.00
12 - 8 3 1.60
- Manitoba 41 9 3.73
- Ontario-North 9 3 2.25
- Ontario-South 52 14 4.00
- Quebec 53 18 4.42
- - 155 18 3.87
This topic can be found in: SQL Reference, Volume 1.