Values and Subtotals - Revision history

Connor at 15:10, 2 August 2012

2012-08-02T15:10:11Z

Connor at 15:06, 2 August 2012

2012-08-02T15:06:56Z

Connor at 15:05, 2 August 2012

2012-08-02T15:05:40Z

Connor at 13:21, 2 August 2012

2012-08-02T13:21:24Z

Connor at 13:06, 2 August 2012

2012-08-02T13:06:50Z

Connor at 12:05, 2 August 2012

2012-08-02T12:05:13Z

Connor at 15:13, 30 July 2012

2012-07-30T15:13:57Z

Connor at 13:47, 30 July 2012

2012-07-30T13:47:47Z

Connor: Created page with "Get values and Subtotals in one shot.

schema:scott
DROP TABLE source <div class=params>schema:scott</div>
<source lang=sql class='tidy'>DROP TABLE source</source>
<source lang=sql class='setup'> CREATE TABLE source(
item VARCHAR(20),
serialnumber INTEGER,
price INTEGER );
INSERT INTO customer VALUES ('Awl',1,10);
INSERT INTO customer VALUES ('Awl',3,10);
INSERT INTO customer VALUES ('Bowl',2,10);
INSERT INTO customer VALUES ('Bowl',5,10);
INSERT INTO customer VALUES ('Bowl'',6,10);
INSERT INTO customer VALUES ('Cowl',4,10);
</source>
<div>
<p>In this example a <code>UNION</code> is used to make the query show the subtotal
results along with the price results and to ensure the subtotals come after
the price a <code>COALESCE</code> function is also used.</p>
</div>
<source lang='sql' class='def'>SELECT item, serialnumber, price FROM(
SELECT item, serialnumber, price FROM source
UNION
SELECT item, NULL, SUM(price)
FROM source
GROUP BY item ) t
ORDER BY item, COALESCE(serialnumber,1E9)</source>
<div class="ecomm e-mysql" style="display: none"></div>
<div class="ecomm e-oracle" style="display: none"></div>
</div>
{{Hacks Ref}}

@@ Line 57: / Line 57: @@
             COALESCE(serialnumber,1E9)</source>
 <div class="ecomm e-mysql" style="display: none">Grand total can also be obtained through <code>ROLLUPS</code>:
- SELECT item, serialnumber, SUM(price)
+<pre>SELECT item, serialnumber, SUM(price)
     FROM serial
-    GROUP BY item, serialnumber WITH ROLLUP</div>
+    GROUP BY item, serialnumber WITH ROLLUP</pre></div>
 <div class="ecomm e-sqlserver" style="display: none">Grand total can also be obtained through <code>ROLLUPS</code>:
- SELECT item, serialnumber, SUM(price)
+<pre>SELECT item, serialnumber, SUM(price)
     FROM serial
-    GROUP BY item, serialnumber WITH ROLLUP</div>
+    GROUP BY item, serialnumber WITH ROLLUP</pre></div>
 <div class="ecomm e-oracle" style="display: none">Grand total can be obtained through <code>GROUPING SETS</code>:
- SELECT item, serialnumber, SUM(price)
+<pre>SELECT item, serialnumber, SUM(price)
     FROM serial
-    GROUP BY GROUPING SETS ((item,serialnumber),(item),())</div>
+    GROUP BY GROUPING SETS ((item,serialnumber),(item),())</pre></div>
 <div class="ecomm e-db2" style="display: none">Both oracle and MySQL methods work for DB2</div>
 </div>
 {{Hacks Ref}}

@@ Line 54: / Line 54: @@
             FROM serial
             GROUP BY item ) t
-            ORDER BY item, COALESCE(serialnumber,1E9)</source>
+            ORDER BY item,
 <div class="ecomm e-mysql" style="display: none">Grand total can also be obtained through <code>ROLLUPS</code>:
-SELECT item, serialnumber, SUM(price)
+ SELECT item, serialnumber, SUM(price)
-  FROM serial
+   FROM serial
-  GROUP BY item, serialnumber WITH ROLLUP</div>
+   GROUP BY item, serialnumber WITH ROLLUP</div>
 <div class="ecomm e-sqlserver" style="display: none">Grand total can also be obtained through <code>ROLLUPS</code>:
-SELECT item, serialnumber, SUM(price)
+ SELECT item, serialnumber, SUM(price)
-  FROM serial
+   FROM serial
-  GROUP BY item, serialnumber WITH ROLLUP</div>
+   GROUP BY item, serialnumber WITH ROLLUP</div>
 <div class="ecomm e-oracle" style="display: none">Grand total can be obtained through <code>GROUPING SETS</code>:
-SELECT item, serialnumber, SUM(price)
+ SELECT item, serialnumber, SUM(price)
-  FROM serial
+   FROM serial
-  GROUP BY GROUPING SETS ((item,serialnumber),(item),())</div>
+   GROUP BY GROUPING SETS ((item,serialnumber),(item),())</div>
 <div class="ecomm e-db2" style="display: none">Both oracle and MySQL methods work for DB2</div>
 </div>
 {{Hacks Ref}}

@@ Line 46: / Line 46: @@
 the price a <code>COALESCE</code> function is also used.</p>
 </div>
-<source lang='sql' class='def'>SELECT item, serialnumber, price FROM(
+<source lang='sql' class='def'>
-SELECT item, serialnumber, price FROM serial
+SELECT item, serialnumber, price
-UNION
+  FROM(SELECT item, serialnumber, price
-SELECT item, NULL, SUM(price)
+         FROM serial
-FROM serial
+         UNION
-GROUP BY item ) t
+         SELECT item, NULL, SUM(price)
-ORDER BY item, COALESCE(serialnumber,1E9)</source>
+           FROM serial
 <div class="ecomm e-mysql" style="display: none">Grand total can also be obtained through <code>ROLLUPS</code>:
 SELECT item, serialnumber, SUM(price)
-FROM serial
+  FROM serial
-GROUP BY item, serialnumber WITH ROLLUP</div>
+  GROUP BY item, serialnumber WITH ROLLUP</div>
 <div class="ecomm e-sqlserver" style="display: none">Grand total can also be obtained through <code>ROLLUPS</code>:
 SELECT item, serialnumber, SUM(price)
-FROM serial
+  FROM serial
-GROUP BY item, serialnumber WITH ROLLUP</div>
+  GROUP BY item, serialnumber WITH ROLLUP</div>
 <div class="ecomm e-oracle" style="display: none">Grand total can be obtained through <code>GROUPING SETS</code>:
 SELECT item, serialnumber, SUM(price)
-FROM serial
+  FROM serial
-GROUP BY GROUPING SETS ((item,serialnumber),(item),())</div>
+  GROUP BY GROUPING SETS ((item,serialnumber),(item),())</div>
 <div class="ecomm e-db2" style="display: none">Both oracle and MySQL methods work for DB2</div>
 </div>
 {{Hacks Ref}}

@@ Line 1: / Line 1: @@
 <p>Get values and Subtotals in one shot.</p>
-<p>In this example you are shown how to obtain subtotals in your query, allowing you to easily see <code>SUM</code> results for different objects.</p>
+<p>In this example you are shown how to obtain subtotals in your query, allowing you to easily see <code>SUM</code> results for different elements.</p>
 <p>Table 1 shows the result without the subtotals and Table 2 shows the result with subtotals.</p>
 <div class="ref_section">

@@ Line 1: / Line 1: @@
 <p>Get values and Subtotals in one shot.</p>
-<p>
+<p>In this example you are shown how to obtain subtotals in your query, allowing you to easily see <code>SUM</code> results for different objects.</p>
 <p>Table 1 shows the result without the subtotals and Table 2 shows the result with subtotals.</p>
 <div class="ref_section">
 <table class= "db_ref">
 <caption>Table 1</caption>
-<tr><th align='center'>'''whn'''</th><th>'''description'''</th><th align='center'>'''amount'''</th></tr>
+<tr><th align='center'>'''item'''</th><th>'''serialnumber'''</th><th align='center'>'''price'''</th></tr>
-<tr><td align='left'>2006-11-01</td><td align='left'>Wages</td><td align='left'>50</td></tr>
+<tr><td align='left'>Awl</td><td align='left'>1</td><td align='left'>10</td></tr>
-<tr><td align='left'>2006-11-02</td><td align='left'>Company Store</td><td align='left'>-10</td></tr>
+<tr><td align='left'>Awl</td><td align='left'>3</td><td align='left'>10</td></tr>
-<tr><td align='left'>2006-11-03</td><td align='left'>Company Store</td><td align='left'>-10</td></tr>
+<tr><td align='left'>Bowl</td><td align='left'>2</td><td align='left'>10</td></tr>
-<tr><td align='left'>2006-11-04</td><td align='left'>Company Store</td><td align='left'>-10</td></tr>
+<tr><td align='left'>Bowl</td><td align='left'>5</td><td align='left'>10</td></tr>
-<tr><td align='left'>2006-11-05</td><td align='left'>Company Store</td><td align='left'>-10</td></tr>
+<tr><td align='left'>Bowl</td><td align='left'>6</td><td align='left'>10</td></tr>
-<tr><td align='left'>2006-11-06</td><td align='left'>Company Store</td><td align='left'>-10</td></tr>
+<tr><td align='left'>Cowl</td><td align='left'>4</td><td align='left'>10</td></tr>
 </table>
 <table class="db_ref">
 <caption>Table 2</caption>
-<tr><th align='center'>'''whn'''</th><th>'''description'''</th><th align='center'>'''amount'''</th></tr>
+<tr><th align='center'>'''item'''</th><th>'''serialnumber'''</th><th align='center'>'''price'''</th></tr>
-<tr><td align='left'>2006-11-01</td><td align='left'>Wages</td><td align='left'>50</td></tr>
+<tr><td align='left'>Awl</td><td align='left'>1</td><td align='left'>10</td></tr>
-<tr><td align='left'>2006-11-02</td><td align='left'>Company Store</td><td align='left'>-10</td></tr>
+<tr><td align='left'>Awl</td><td align='left'>3</td><td align='left'>10</td></tr>
-<tr><td align='left'>2006-11-03</td><td align='left'>Company Store</td><td align='left'>-10</td></tr>
+<tr><td align='left'>Awl</td><td align='left'></td><td align='left'>20</td></tr>
-<tr><td align='left'>2006-11-04</td><td align='left'>Company Store</td><td align='left'>-10</td></tr>
+<tr><td align='left'>Bowl</td><td align='left'>2</td><td align='left'>10</td></tr>
-<tr><td align='left'>2006-11-05</td><td align='left'>Company Store</td><td align='left'>-10</td></tr>
+<tr><td align='left'>Bowl</td><td align='left'>5</td><td align='left'>10</td></tr>
-<tr><td align='left'>2006-11-06</td><td align='left'>Company Store</td><td align='left'>-10</td></tr>
+<tr><td align='left'>Bowl</td><td align='left'>6</td><td align='left'>10</td></tr>
 </table>
 </div>

@@ Line 1: / Line 1: @@
-Get values and Subtotals in one shot.
+<p>Get values and Subtotals in one shot.</p>
 <div class='ht'>
 <div class=params>schema:scott</div>
-<source lang=sql class='tidy'>DROP TABLE source</source>
+<source lang=sql class='tidy'>DROP TABLE serial</source>
-<source lang=sql class='setup'> CREATE TABLE source(
+<source lang=sql class='setup'> CREATE TABLE serial(
    item VARCHAR(20),
    serialnumber INTEGER,
    price INTEGER );
-INSERT INTO source VALUES ('Awl',1,10);
+INSERT INTO serial VALUES ('Awl',1,10);
-INSERT INTO source VALUES ('Awl',3,10);
+INSERT INTO serial VALUES ('Awl',3,10);
-INSERT INTO source VALUES ('Bowl',2,10);
+INSERT INTO serial VALUES ('Bowl',2,10);
-INSERT INTO source VALUES ('Bowl',5,10);
+INSERT INTO serial VALUES ('Bowl',5,10);
-INSERT INTO source VALUES ('Bowl'',6,10);
+INSERT INTO serial VALUES ('Bowl',6,10);
-INSERT INTO source VALUES ('Cowl',4,10);
+INSERT INTO serial VALUES ('Cowl',4,10);
 </source>
 <div>
@@ Line 20: / Line 44: @@
 </div>
 <source lang='sql' class='def'>SELECT item, serialnumber, price FROM(
-SELECT item, serialnumber, price FROM source
+SELECT item, serialnumber, price FROM serial
 UNION
 SELECT item, NULL, SUM(price)
-FROM source
+FROM serial
 GROUP BY item ) t
 ORDER BY item, COALESCE(serialnumber,1E9)</source>
 <div class="ecomm e-mysql" style="display: none">Grand total can also be obtained through <code>ROLLUPS</code>:
 SELECT item, serialnumber, SUM(price)
-FROM source
+FROM serial
 GROUP BY item, serialnumber WITH ROLLUP</div>
 <div class="ecomm e-sqlserver" style="display: none">Grand total can also be obtained through <code>ROLLUPS</code>:
 SELECT item, serialnumber, SUM(price)
-FROM source
+FROM serial
 GROUP BY item, serialnumber WITH ROLLUP</div>
 <div class="ecomm e-oracle" style="display: none">Grand total can be obtained through <code>GROUPING SETS</code>:
 SELECT item, serialnumber, SUM(price)
-FROM source
+FROM serial
 GROUP BY GROUPING SETS ((item,serialnumber),(item),())</div>
 <div class="ecomm e-db2" style="display: none">Both oracle and MySQL methods work for DB2</div>
 </div>
 {{Hacks Ref}}