.-,--------------------------.
V |
>>-GROUP BY------+-grouping-expression-+--+--------------------><
+-grouping-sets-------+
'-super-groups--------'
|
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). The length attribute of each grouping-expression must not be more than 254 bytes (SQLSTATE 42907). 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 iscol1+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.
.-,---------------------------------------------.
V |
>>-GROUPING SETS--(------+--+-grouping-expression-+---------------+--+---)-->
| '-super-groups--------' |
| .-,--------------------------. |
| V | |
'-(------+-grouping-expression-+--+---)--'
'-super-groups--------'
>--------------------------------------------------------------><
|
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 block 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 SET((a))
and
GROUP BY a,b,c
is the same as
GROUP BY GROUPING SET((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. See GROUPING for how to distinguish rows with nulls in actual data from rows with nulls generated from grouping sets.
Example C2 through Example C7 illustrate the use of grouping sets.
(1)
>>-+-ROLLUP--(--grouping-expression-list--)-------+------------><
| (2) |
+-CUBE--(--grouping-expression-list--)---------+
'-| grand-total |------------------------------'
grouping-expression-list
.-,-----------------------------------------.
V |
|-------+-grouping-expression----------------+--+---------------|
| .-,----------------------. |
| V | |
'-(-----grouping-expression---+---)--'
grand-total
|---(--)--------------------------------------------------------|
Notes:
|
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)
() )
Notice that the n elements of the ROLLUP translate to n+1 grouping sets.
Note 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) )
>>-HAVING--search-condition------------------------------------>< |
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 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.
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 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
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 sinlge 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
(42) These are called sub-total rows, because that is their most common use, however any column function can be used for the aggregation. For instance, MAX and AVG are used in Example C8.