Difference between revisions of "Values and Subtotals"

From SQLZOO
Jump to: navigation, search
 
(3 intermediate revisions by one user not shown)
Line 1: Line 1:
 
<p>Get values and Subtotals in one shot.</p>
 
<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>  
 
<p>Table 1 shows the result without the subtotals and Table 2 shows the result with subtotals.</p>  
 
<div class="ref_section">
 
<div class="ref_section">
Line 46: 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 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
 +
          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>:
 
<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
+
  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>:
 
<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
+
  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>:
 
<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
+
  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 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 15: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)
SQL HACKS

Hack 10 Converting subqueries into joins

Hack 11 Converting aggregate subqueries into joins

Hack 14 Generate Combinations

Hack 16 Search for a String across columns

Hack 24 Multiply Across a Result Set

Hack 25 Keep a running total

Hack 25.5 Splitting and combining columns

Hack 26 Include the rows your JOIN forgot

Hack 30 Calculate the maximum/minimum of two fields

Hack 33 Get values and subtotals in one shot

Hack 50 Combine tables containing different data

Hack 51/52 Display rows as columns

Hack 55 Import Someone Else's Data

Hack 62 Issue Queries Without Using a Table

Hack 63 Generate rows without tables

Hack 70 Combine your queries

Hack 72 Extract a subset of the results

Hack 78 Break it down by Range

Hack 88 Test two values from a subquery

Hack 98 Find and stop long running queries

Hack 100 Run SQL from a Web Page

Retrieved from "http://sqlzoo.net/w/index.php?title=Values_and_Subtotals&oldid=3016"
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense