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...")
 
Line 26: Line 26:
 
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"></div>
+
<div class="ecomm e-mysql" style="display: none">Grand total can also be obtained through <code>ROLLUPS</code>:
<div class="ecomm e-oracle" style="display: none"></div>
+
SELECT item, serialnumber, SUM(price)
 +
FROM source
 +
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
 +
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
 +
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>
 
</div>
 
{{Hacks Ref}}
 
{{Hacks Ref}}

Revision as of 14:47, 30 July 2012

Get values and Subtotals in one shot.

schema:scott
DROP TABLE SOURCE
 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);

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 SOURCE
UNION
SELECT item, NULL, SUM(price)
FROM SOURCE
GROUP BY item ) t
ORDER BY item, COALESCE(serialnumber,1E9)
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense