DB2 Version 9.7 for Linux, UNIX, and Windows
DB2 Version 9.7 for Linux, UNIX, and Windows
>>-select-clause--from-clause--+--------------+-----------------> '-where-clause-' >--+-----------------+--+---------------+-----------------------> '-group-by-clause-' '-having-clause-' >--+-----------------+--+--------------------+------------------> '-order-by-clause-' '-fetch-first-clause-' >--+------------------+---------------------------------------->< '-isolation-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 can be quite different from this description. If portions of the subselect do not actually need to be executed for the correct result to be obtained, they might or might not be executed.)
The authorization for a subselect is described in the Authorization section in "SQL queries".
SELECT * FROM T1
ORDER BY C1
UNION
SELECT * FROM T2
ORDER BY C1(SELECT * FROM T1
ORDER BY C1)
UNION
(SELECT * FROM T2
ORDER BY C1)
Two rows are duplicates of one another only if each value in the first is equal to the corresponding value in the second. For determining duplicates, two null values are considered equal, and two different decimal floating-point representations of the same number are considered equal. For example, -0 is equal to +0 and 2.0 is equal to 2.00. Each of the decimal floating-point special values are also considered equal: -NAN equals -NAN, -SNAN equals -SNAN, -INFINITY equals -INFINITY, INFINITY equals INFINITY, SNAN equals SNAN, and NAN equals NAN.
When the data type of a column is decimal floating-point, and multiple representations of the same number exist in the column, the particular value that is returned for a SELECT DISTINCT can be any one of the representations in the column. For more information, see Numeric comparisons.
For compatibility with other SQL implementations, UNIQUE can be specified as a synonym for DISTINCT.
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.
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, or the same precision for DECFLOAT columns. |
| a constant | the same as the data type of the constant. |
| the name of any numeric variable | the same as the data type of the variable, with the same precision and scale for DECIMAL variables, or the same precision for DECFLOAT variables. |
| 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. |
| 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 only 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-reference (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 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.
If a correlation-clause does not follow a function-name reference, xmltable-expression, nested table expression, or data-change-table-reference, there is no exposed name for that table reference.
A collection-derived-table can be used to convert the elements of an array into values of a column in separate rows. If WITH ORDINALITY is specified, an extra column of data type INTEGER is appended. This column contains the position of the element in the array. The columns can be referenced in the select list and the rest of the subselect by using the names specified for the columns in the correlation-clause. The collection-derived-table clause can only be used in a context where arrays are supported (SQLSTATE 42887). See the "UNNEST table function" for details.
If a view is referenced directly in, or as the target of a nested table expression in a data change statement within a FROM clause, the view must either be symmetric (have WITH CHECK OPTION specified) or satisfy the restriction for a WITH CHECK OPTION view.
If the target of a data change statement within a FROM clause is a nested table expression, the modified rows are not requalified, WHERE clause predicates are not re-evaluated, and ORDER BY or FETCH FIRST operations are not redone.
The optional tablesample-clause can be used to obtain a random subset (a sample) of the rows from the specified table-name, rather than the entire contents of that table-name, for this query. This sampling is in addition to any predicates that are specified in the where-clause. Unless the optional REPEATABLE clause is specified, each execution of the query will usually yield a different sample, except in degenerate cases where the table is so small relative to the sample size that any sample must return the same rows. The size of the sample is controlled by the numeric-expression1 in parentheses, representing an approximate percentage (P) of the table to be returned. The method by which the sample is obtained is specified after the TABLESAMPLE keyword, and can be either BERNOULLI or SYSTEM. For both methods, the exact number of rows in the sample may be different for each execution of the query, but on average should be approximately P percent of the table, before any predicates further reduce the number of rows.
The table-name must be a stored table. It can be a materialized query table (MQT) name, but not a subselect or table expression for which an MQT has been defined, because there is no guarantee that the database manager will route to the MQT for that subselect.
Semantically, sampling of a table occurs before any other query processing, such as applying predicates or performing joins. Repeated accesses of a sampled table within a single execution of a query (such as in a nested-loop join or a correlated subquery) will return the same sample. More than one table may be sampled in a query.
BERNOULLI sampling considers each row individually. It includes each row in the sample with probability P/100 (where P is the value of numeric-expression1), and excludes each row with probability 1 - P/100, independently of the other rows. So if the numeric-expression1 evaluated to the value 10, representing a ten percent sample, each row would be included with probability 0.1, and excluded with probability 0.9.
SYSTEM sampling permits the database manager to determine the most efficient manner in which to perform the sampling. In most cases, SYSTEM sampling applied to a table-name means that each page of table-name is included in the sample with probability P/100, and excluded with probability 1 - P/100. All rows on each page that is included qualify for the sample. SYSTEM sampling of a table-name generally executes much faster than BERNOULLI sampling, because fewer data pages need to be retrieved; however, it can often yield less accurate estimates for aggregate functions (SUM(SALES), for example), especially if the rows of table-name are clustered on any columns referenced in that query. The optimizer may in certain circumstances decide that it is more efficient to perform SYSTEM sampling as if it were BERNOULLI sampling, for example when a predicate on table-name can be applied by an index and is much more selective than the sampling rate P.
The numeric-expression1 specifies the size of the sample to be obtained from table-name, expressed as a percentage. It must be a constant numeric expression that cannot contain columns. The expression must evaluate to a positive number that is less than or equal to 100, but can be between 1 and 0. For example, a value of 0.01 represents one one-hundredth of a percent, meaning that 1 row in 10 000 would be sampled, on average. A numeric-expression1 that evaluates to 100 is handled as if the tablesample-clause were not specified. If numeric-expression1 evaluates to the null value, or to a value that is greater than 100 or less than 0, an error is returned (SQLSTATE 2202H).
It is sometimes desirable for sampling to be repeatable from one execution of the query to the next; for example, during regression testing or query "debugging". This can be accomplished by specifying the REPEATABLE clause. The REPEATABLE clause requires the specification of a numeric-expression2 in parentheses, which serves the same role as the seed in a random number generator. Adding the REPEATABLE clause to the tablesample-clause of any table-name ensures that repeated executions of that query (using the same value for numeric-expression2) return the same sample, assuming, of course, that the data itself has not been updated, reorganized, or repartitioned. To guarantee that the same sample of table-name is used across multiple queries, use of a global temporary table is recommended. Alternatively, the multiple queries could be combined into one query, with multiple references to a sample that is defined using the WITH clause.
Following are some examples:
SELECT * FROM Sales
TABLESAMPLE BERNOULLI(10)SELECT SUM(Sales.Revenue) / (0.01)
FROM Sales TABLESAMPLE SYSTEM(1)
WHERE Sales.RegionName = 'Northeast'
GROUP BY Sales.ProductCategorySELECT SUM(Sales.Revenue) / (0.01)
FROM Sales TABLESAMPLE SYSTEM(1) REPEATABLE(3578231)
WHERE Sales.RegionName = 'Northeast'
GROUP BY Sales.ProductCategoryIn 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.
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 Example 1: SELECT c1
FROM TABLE( tfmod('Jones') ) AS z
Example 2: SELECT c1
FROM t1, t2, TABLE( tfmod(t1.c1, t2.c1) ) AS z
Example 3: SET var =
(SELECT c1
FROM TABLE( tfmod('Jones') ) AS z
Example 4: RETURN SELECT c1
FROM TABLE( tfmod('Jones') ) AS z
Example 5: WITH v1(c1) AS
(SELECT c1
FROM TABLE( tfmod(:hostvar1) ) AS z)
SELECT c1
FROM v1, t1 WHERE v1.c1 = t1.c1Certain errors that occur within a nested-table-expression can be tolerated, and instead of returning an error, the query can continue and return a result.
Specifying the RETURN DATA UNTIL clause will cause any rows that are returned from the fullselect before the indicated condition is encountered to make up the result set from the fullselect. This means that a partial result set (which could also be an empty result set) from the fullselect is acceptable as the result for the nested-table-expression.
The FEDERATED keyword restricts the condition to handle only errors that occur at a remote data source.
The condition can be specified as an SQLSTATE value, with a string-constant length of 5. You can optionally specify an SQLCODE value for each specified SQLSTATE value. For portable applications, specify SQLSTATE values as much as possible, because SQLCODE values are generally not portable across platforms and are not part of the SQL standard.
Only certain conditions can be tolerated. Errors that do not allow the rest of the query to be executed cannot be tolerated, and an error is returned for the whole query. The specific-condition-value might specify conditions that cannot actually be tolerated by the database manager, even if a specific SQLSTATE or SQLCODE value is specified, and for these cases, an error is returned.
A query or view containing an error tolerant nested-table-expression is read-only.
The fullselect of an error tolerant nested-table-expression is not optimized using materialized query tables.
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 tf4
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,
LATERAL (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 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 unknownA data-change-table-reference clause specifies an intermediate result table. This table is based on the rows that are directly changed by the searched UPDATE, searched DELETE, or INSERT statement that is included in the clause. A data-change-table-reference can be specified as the only table-reference in the FROM clause of the outer fullselect that is used in a select-statement, a SELECT INTO statement, or a common table expression. A data-change-table-reference can be specified as the only table reference in the only fullselect in a SET Variable statement (SQLSTATE 428FL). The target table or view of the data change statement is considered to be a table or view that is referenced in the query; therefore, the authorization ID of the query must have SELECT privilege on that target table or view. A data-change-table-reference clause cannot be specified in a view definition, materialized query table definition, or FOR statement (SQLSTATE 428FL).
The target of the UPDATE, DELETE, or INSERT statement cannot be a temporary view defined in a common table expression (SQLSTATE 42807) or a nickname (SQLSTATE 25000).
The content of the intermediate result table for a data-change-table-reference is determined when the cursor opens. The intermediate result table contains all manipulated rows, including all the columns in the specified target table or view. All the columns of the target table or view for an SQL data change statement are accessible using the column names from the target table or view. If an INCLUDE clause was specified within a data change statement, the intermediate result table will contain these additional columns.
ROUTINE_SOURCE_TABLE has a defined structure (see ANALYZE_TABLE examples) and, in a partitioned database environment, must reside on the catalog database partition. The table cannot be a global temporary table. The MODELDS2 column for a given row must not be empty or contain the null value; if the value of the MODELFORMATS or MODELMETADATA column is not null, the value must have length greater than 0. If no table schema name is specified, CURRENT SCHEMA is assumed.
IMPLEMENTATION
'PROVIDER=SAS;
ROUTINE_SOURCE_TABLE=ETLIN.SOURCE_TABLE;
ROUTINE_SOURCE_NAME=SCORING_FUN1;'IMPLEMENTATION
'PROVIDER=SAS;
ROUTINE_SOURCE_TABLE="ETLin"."Source_Table";
ROUTINE_SOURCE_NAME="Scoring_Fun1";'The following examples show you how to use the ANALYZE_TABLE expression.
CREATE TABLE ETLIN.SOURCE_TABLE (
MODELNAME VARCHAR(128) NOT NULL PRIMARY KEY,
MODELDS2 BLOB(4M) NOT NULL,
MODELFORMATS BLOB(4M),
MODELMETADATA BLOB(4M)
); WITH sas_score_in (c1,c2) AS
(SELECT c1,c2 FROM t1)
SELECT *
FROM sas_score_in ANALYZE_TABLE(
IMPLEMENTATION
'PROVIDER=SAS;
ROUTINE_SOURCE_TABLE=ETLIN.SOURCE_TABLE;
ROUTINE_SOURCE_NAME=SCORING_FUN1;'); SELECT *
FROM t2 ANALYZE_TABLE(
IMPLEMENTATION
'PROVIDER=SAS;
ROUTINE_SOURCE_TABLE=ETLIN.SOURCE_TABLE;
ROUTINE_SOURCE_NAME=SCORING_FUN2;'); SELECT *
FROM v1 ANALYZE_TABLE(
IMPLEMENTATION
'PROVIDER=SAS;
ROUTINE_SOURCE_TABLE=ETLIN.SOURCE_TABLE;
ROUTINE_SOURCE_NAME=SCORING_FUN3;')
ORDER BY 1;
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: CROSS, INNER, LEFT OUTER, RIGHT OUTER, or FULL OUTER to its operands.
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 (TB1 LEFT JOIN TB2 ON TB1.C1=TB2.C1)
RIGHT JOIN (TB3 LEFT JOIN TB4 ON TB3.C1=TB4.C1)
ON TB1.C1=TB3.C1A 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.
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 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.
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 expression or 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 or an XMLQUERY or XMLEXISTS expression (SQLSTATE 42822), or any expression or function that is not deterministic 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.
If a grouping-expression contains decimal floating-point columns, and multiple representations of the same number exist in these columns, the number that is returned can be any of the representations of the number.
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, not deterministic 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.
GROUP BY a GROUP BY GROUPING SETS((a)) GROUP BY a,b,c 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.
GROUP BY ROLLUP(C1,C2,...,Cn-1,Cn) GROUP BY GROUPING SETS((C1,C2,...,Cn-1,Cn)
(C1,C2,...,Cn-1)
...
(C1,C2)
(C1)
() ) GROUP BY ROLLUP(a,b) GROUP BY GROUPING SETS((a,b)
(a)
() ) GROUP BY ROLLUP(b,a) 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.
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)
() )Note that the three elements of the CUBE translate into eight 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 subtotal 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)
() )and the three-element ROLLUP generates:
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.
GROUP BY a, ROLLUP(b,c) GROUP BY GROUPING SETS((a,b,c)
(a,b)
(a) ) GROUP BY a, b, ROLLUP(c,d) GROUP BY GROUPING SETS((a,b,c,d)
(a,b,c)
(a,b) ) 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)
() ) GROUP BY a, ROLLUP(a,b) 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.
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.
A group of R to which the search condition is applied supplies the argument for each aggregate 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 an aggregate function.
When HAVING is used without GROUP BY, the select list can only include column names when they are arguments to an aggregate function, correlated column references, global variables, host variables, literals, special registers, SQL variables, or SQL parameters.
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 CLOB, DBCLOB, BLOB, XML, 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. If an ORDER BY clause contains decimal floating-point columns, and multiple representations of the same number exist in these columns, the ordering of the multiple representations of the same number is unspecified. 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.
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.
The sort-key-expression must match exactly with an expression in the select list of the subselect (scalar-fullselects are never matched).
(SELECT C1 FROM T1
ORDER BY C1)
UNION
SELECT C1 FROM T2
ORDER BY ORDER OF T1SELECT C1 FROM
(SELECT C1 FROM T1
UNION
SELECT C1 FROM T2
ORDER BY C1 ) AS UTABLE
ORDER BY ORDER OF UTABLEThe 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).
Use of the fetch-first-clause influences query optimization of the subselect or fullselect, based on the fact that at most integer rows will be retrieved. If both the fetch-first-clause is specified in the outermost fullselect and the optimize-for-clause is specified for the select statement, the database manager will use the integer from the optimize-for-clause to influence query optimization of the outermost fullselect.
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.
If the fullselect contains an SQL data change statement in the FROM clause, all the rows are modified regardless of the limit on the number of rows to fetch.
The lock-request-clause applies only to queries and to positioning read operations within an insert, update, or delete operation. The insert, update, and delete operations themselves will execute using locking determined by the database manager.
create view a as (...);
(select * from a with RR USE AND KEEP SHARE LOCKS)
UNION ALL
(select * from a with UR);WITH a as (...)
(select * from a with RR USE AND KEEP SHARE LOCKS)
UNION ALL
(select * from a with UR); SELECT * FROM EMPLOYEE SELECT EMP_ACT.*, LASTNAME
FROM EMP_ACT, EMPLOYEE
WHERE EMP_ACT.EMPNO = EMPLOYEE.EMPNO SELECT EMPNO, LASTNAME, WORKDEPT, DEPTNAME
FROM EMPLOYEE, DEPARTMENT
WHERE WORKDEPT = DEPTNO
AND YEAR(BIRTHDATE) < 1930 SELECT JOB, MIN(SALARY), MAX(SALARY)
FROM EMPLOYEE
GROUP BY JOB
HAVING COUNT(*) > 1
AND MAX(SALARY) >= 27000 SELECT *
FROM EMP_ACT
WHERE EMPNO IN
(SELECT EMPNO
FROM EMPLOYEE
WHERE WORKDEPT = 'E11') SELECT WORKDEPT, MAX(SALARY)
FROM EMPLOYEE
GROUP BY WORKDEPT
HAVING MAX(SALARY) < (SELECT AVG(SALARY)
FROM EMPLOYEE) 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.
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.DEPTNOUsing 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.
SELECT RANDID , AVG(EDLEVEL), AVG(SALARY)
FROM ( SELECT EDLEVEL, SALARY, INTEGER(RAND()*5) AS RANDID
FROM EMPLOYEE
) AS EMPRAND
GROUP BY RANDID 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) SELECT T.PHONE, T.ID, T.INDEX FROM UNNEST(PHONES, IDS)
WITH ORDINALITY AS T(PHONE, ID, INDEX)
ORDER BY T.INDEXExample 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 23The 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 22In 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=YThe 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 22The 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 23The 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 22The 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 22The 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=12Hence, 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 - - SELECT DEPTNO, DEPTNAME, EMPNO, LASTNAME
FROM DEPARTMENT LEFT OUTER JOIN EMPLOYEE
ON MGRNO = EMPNO 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 = DEPTNOThe 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) = 13which 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 1Example 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_PERSONThis 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 4Example 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_PERSONThis 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 4The rows with WEEK 13 are from the first grouping set and the other rows are from the second grouping set.
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_PERSONThis 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 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 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 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 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
- - 155Example 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.
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_WEEKresults 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
- - 155Example 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 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 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 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
- - - - 155Using the two ROLLUPs as grouping sets causes the result to include duplicate rows. There are even two grand total rows.
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.
SELECT R1,R2
FROM (VALUES('GROUP 1','GROUP 2')) AS X(R1,R2); R1 R2
------- -------
GROUP 1 GROUP 2 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.
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 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 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; 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 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, REGIONMONTH 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