Difference between revisions of "Values and Subtotals"

From SQLZOO
Jump to: navigation, search
(Created page with "Get values and Subtotals in one shot. <div class='ht'> <div class=params>schema:scott</div> <source lang=sql class='tidy'>DROP TABLE source</source> <source lang=sql class='se...")
 
 
(7 intermediate revisions by one user not shown)
Line 1: Line 1:
Get values and Subtotals in one shot.
+
<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 elements.</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'>'''item'''</th><th>'''serialnumber'''</th><th align='center'>'''price'''</th></tr>
 +
<tr><td align='left'>Awl</td><td align='left'>1</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'>Bowl</td><td align='left'>2</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'>Bowl</td><td align='left'>6</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'>'''item'''</th><th>'''serialnumber'''</th><th align='center'>'''price'''</th></tr>
 +
<tr><td align='left'>Awl</td><td align='left'>1</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'>Awl</td><td align='left'></td><td align='left'>20</td></tr>
 +
<tr><td align='left'>Bowl</td><td align='left'>2</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'>Bowl</td><td align='left'>6</td><td align='left'>10</td></tr>
 +
<tr><td align='left'>Bowl</td><td align='left'></td><td align='left'>30</td></tr>
 +
<tr><td align='left'>Cowl</td><td align='left'>4</td><td align='left'>10</td></tr>
 +
<tr><td align='left'>Cowl</td><td align='left'></td><td align='left'>10</td></tr>
 +
</table>
 +
</div>
 
<div class='ht'>
 
<div class='ht'>
 
<div class=params>schema:scott</div>
 
<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),
 
   item VARCHAR(20),
 
   serialnumber INTEGER,
 
   serialnumber INTEGER,
 
   price INTEGER );
 
   price INTEGER );
INSERT INTO customer VALUES ('Awl',1,10);
+
INSERT INTO serial VALUES ('Awl',1,10);
INSERT INTO customer VALUES ('Awl',3,10);
+
INSERT INTO serial VALUES ('Awl',3,10);
INSERT INTO customer VALUES ('Bowl',2,10);
+
INSERT INTO serial VALUES ('Bowl',2,10);
INSERT INTO customer VALUES ('Bowl',5,10);
+
INSERT INTO serial VALUES ('Bowl',5,10);
INSERT INTO customer VALUES ('Bowl'',6,10);
+
INSERT INTO serial VALUES ('Bowl',6,10);
INSERT INTO customer VALUES ('Cowl',4,10);
+
INSERT INTO serial VALUES ('Cowl',4,10);
 
</source>
 
</source>
 
<div>
 
<div>
Line 19: Line 46:
 
the price a <code>COALESCE</code> function is also used.</p>  
 
the price a <code>COALESCE</code> function is also used.</p>  
 
</div>
 
</div>
<source lang='sql' class='def'>SELECT item, serialnumber, price FROM(
+
<source lang='sql' class='def'>
SELECT item, serialnumber, price FROM source
+
SELECT item, serialnumber, price  
UNION
+
  FROM(SELECT item, serialnumber, price  
SELECT item, NULL, SUM(price)
+
        FROM serial
FROM source
+
        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"></div>
+
          GROUP BY item ) t
<div class="ecomm e-oracle" style="display: none"></div>
+
          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>:
 +
<pre>SELECT item, serialnumber, SUM(price)
 +
  FROM serial
 +
  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>:
 +
<pre>SELECT item, serialnumber, SUM(price)
 +
  FROM serial
 +
  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>:
 +
<pre>SELECT item, serialnumber, SUM(price)
 +
  FROM serial
 +
  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>
 
</div>
 
{{Hacks Ref}}
 
{{Hacks Ref}}

Latest revision as of 16:10, 2 August 2012

Get values and Subtotals in one shot.

In this example you are shown how to obtain subtotals in your query, allowing you to easily see SUM results for different elements.

Table 1 shows the result without the subtotals and Table 2 shows the result with subtotals.

Table 1
itemserialnumberprice
Awl110
Awl310
Bowl210
Bowl510
Bowl610
Cowl410
Table 2
itemserialnumberprice
Awl110
Awl310
Awl20
Bowl210
Bowl510
Bowl610
Bowl30
Cowl410
Cowl10
schema:scott
DROP TABLE serial
 CREATE TABLE serial(
  item VARCHAR(20),
  serialnumber INTEGER,
  price INTEGER );
INSERT INTO serial VALUES ('Awl',1,10);
INSERT INTO serial VALUES ('Awl',3,10);
INSERT INTO serial VALUES ('Bowl',2,10);
INSERT INTO serial VALUES ('Bowl',5,10);
INSERT INTO serial VALUES ('Bowl',6,10);
INSERT INTO serial VALUES ('Cowl',4,10);

In this example a UNION 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 COALESCE function is also used.

SELECT item, serialnumber, price 
  FROM(SELECT item, serialnumber, price 
         FROM serial
         UNION
         SELECT item, NULL, SUM(price)
           FROM serial
           GROUP BY item ) t
           ORDER BY item, 
           COALESCE(serialnumber,1E9)
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense