Difference between revisions of "Values and Subtotals"

From SQLZOO
Jump to: navigation, search
Line 54: Line 54:
 
           FROM serial
 
           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 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>:
 
<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>:
 
<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 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 15:06, 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