Difference between revisions of "Values and Subtotals"

From SQLZOO
Jump to: navigation, search
Line 1: Line 1:
Get values and Subtotals in one shot.
+
<p>Get values and Subtotals in one shot.</p>
 +
<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><td align='left'>2006-11-01</td><td align='left'>Wages</td><td align='left'>50</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'>2006-11-03</td><td align='left'>Company Store</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'>2006-11-05</td><td align='left'>Company Store</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>
 +
</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><td align='left'>2006-11-01</td><td align='left'>Wages</td><td align='left'>50</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'>2006-11-03</td><td align='left'>Company Store</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'>2006-11-05</td><td align='left'>Company Store</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>
 +
</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 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>
 
</source>
 
<div>
 
<div>
Line 20: Line 44:
 
</div>
 
</div>
 
<source lang='sql' class='def'>SELECT item, serialnumber, price FROM(
 
<source lang='sql' class='def'>SELECT item, serialnumber, price FROM(
SELECT item, serialnumber, price FROM source
+
SELECT item, serialnumber, price FROM serial
 
UNION
 
UNION
 
SELECT item, NULL, SUM(price)
 
SELECT item, NULL, SUM(price)
FROM source
+
FROM serial
 
GROUP BY item ) t
 
GROUP BY item ) t
 
ORDER BY item, COALESCE(serialnumber,1E9)</source>
 
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>:
 
<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 source
+
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>:
 
<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 source
+
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>:
 
<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 source
+
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 class="ecomm e-db2" style="display: none">Both oracle and MySQL methods work for DB2</div>
 
</div>
 
</div>
 
{{Hacks Ref}}
 
{{Hacks Ref}}

Revision as of 12:05, 2 August 2012

Get values and Subtotals in one shot.

<p>Table 1 shows the result without the subtotals and Table 2 shows the result with subtotals.

Table 1
whndescriptionamount
2006-11-01Wages50
2006-11-02Company Store-10
2006-11-03Company Store-10
2006-11-04Company Store-10
2006-11-05Company Store-10
2006-11-06Company Store-10
Table 2
whndescriptionamount
2006-11-01Wages50
2006-11-02Company Store-10
2006-11-03Company Store-10
2006-11-04Company Store-10
2006-11-05Company Store-10
2006-11-06Company Store-10
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