svn commit: r826126 - /db/derby/docs/trunk/src/ref/rrefsqlj32654.dita

[bp...@apache.org]

Author: bpendleton
Date: Fri Oct 16 22:49:24 2009
New Revision: 826126

URL: http://svn.apache.org/viewvc?rev=826126&view=rev
Log:
DERBY-4394: Add documentation for ROLLUP functionality

This change enhances the Reference Guide page for the GROUP BY clause so
that it describes the syntax and behavior of the new ROLLUP style of
grouping. The syntax diagram now incorporates the ROLLUP syntax; the
behavior of the ROLLUP specification is (briefly) described, and there
is an example of a GROUP BY clause containing a ROLLUP specification.


Modified:
    db/derby/docs/trunk/src/ref/rrefsqlj32654.dita

Modified: db/derby/docs/trunk/src/ref/rrefsqlj32654.dita
URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefsqlj32654.dita?rev=826126&r1=826125&r2=826126&view=diff
==============================================================================
--- db/derby/docs/trunk/src/ref/rrefsqlj32654.dita (original)
+++ db/derby/docs/trunk/src/ref/rrefsqlj32654.dita Fri Oct 16 22:49:24 2009
@@ -28,10 +28,19 @@
 a result into subsets that have matching values for one or more columns. In
 each group, no two rows have the same value for the grouping column or columns.
 NULLs are considered equivalent for grouping purposes.</p>  <p>You typically
-use a GROUP BY clause in conjunction with an aggregate expression.</p></section>
-<refsyn><title>Syntax</title> <codeblock><b>GROUP BY <i><xref href="rrefcolumnname.dita#rrefcolumnname">column-Name</xref></i> [ , <i><xref
-href="rrefcolumnname.dita#rrefcolumnname">column-Name</xref></i> ] *</b></codeblock> <p><i><xref
-href="rrefcolumnname.dita#rrefcolumnname">column-Name</xref></i> must be a column from
+use a GROUP BY clause in conjunction with an aggregate expression.</p>
+<p>Using the ROLLUP syntax, you can specify that multiple levels of grouping
+    should be computed at once.</p>
+</section>
+<refsyn><title>Syntax</title>
+    <codeblock><b>
+GROUP BY 
+{
+    <i><xref href="rrefcolumnname.dita#rrefcolumnname">column-Name</xref></i> [ , <i><xref href="rrefcolumnname.dita#rrefcolumnname">column-Name</xref></i> ]*  
+|
+    ROLLUP ( <i><xref href="rrefcolumnname.dita#rrefcolumnname">column-Name</xref></i> [ , <i><xref href="rrefcolumnname.dita#rrefcolumnname">column-Name</xref></i> ]* )
+}</b></codeblock>
+        <p><i><xref href="rrefcolumnname.dita#rrefcolumnname">column-Name</xref></i> must be a column from
 the current scope of the query; there can be no columns from a query block
 outside the current scope. For example, if a GROUP BY clause is in a subquery,
 it cannot refer to columns in the outer query.</p> <p><i>SelectItems</i> in
@@ -47,17 +56,26 @@
 FROM Cities, Countries
 WHERE Cities.country_ISO_code = Countries.country_ISO_code
 GROUP BY region
+
 <ph>-- group by an a smallint</ph>
 SELECT ID, AVG(SALARY)
 FROM SAMP.STAFF
 GROUP BY ID
--- Get the AVGSALARY and EMPCOUNT columns, and the DEPTNO column using the AS clause
--- And group by the WORKDEPT column using the correlation name OTHERS
+
+<ph>-- Get the AVGSALARY and EMPCOUNT columns, and the DEPTNO column using the AS clause
+-- And group by the WORKDEPT column using the correlation name OTHERS</ph>
 SELECT OTHERS.WORKDEPT AS DEPTNO,
 AVG(OTHERS.SALARY) AS AVGSALARY,
 COUNT(*) AS EMPCOUNT
 FROM SAMP.EMPLOYEE OTHERS
 GROUP BY OTHERS.WORKDEPT
+
+<ph>-- Compute sub-totals of Sales_History data, grouping it by Region, by
+-- (Region, State), and by (Region, State, Product), as well as computing
+-- an overall total of the sales for all Regions, States, and Products:</ph>
+SELECT Region, State, Product, SUM(Sales) Total_Sales
+FROM Sales_History 
+GROUP BY ROLLUP(Region, State, Product)
 </b></codeblock> </example>
 </refbody>
 </reference>