Difference between revisions of "Values and Subtotals"

From SQLZOO
Jump to: navigation, search
Line 7: Line 7:
 
   serialnumber INTEGER,
 
   serialnumber INTEGER,
 
   price INTEGER );
 
   price INTEGER );
INSERT INTO customer VALUES ('Awl',1,10);
+
INSERT INTO source VALUES ('Awl',1,10);
INSERT INTO customer VALUES ('Awl',3,10);
+
INSERT INTO source VALUES ('Awl',3,10);
INSERT INTO customer VALUES ('Bowl',2,10);
+
INSERT INTO source VALUES ('Bowl',2,10);
INSERT INTO customer VALUES ('Bowl',5,10);
+
INSERT INTO source VALUES ('Bowl',5,10);
INSERT INTO customer VALUES ('Bowl'',6,10);
+
INSERT INTO source VALUES ('Bowl'',6,10);
INSERT INTO customer VALUES ('Cowl',4,10);
+
INSERT INTO source VALUES ('Cowl',4,10);
 
</source>
 
</source>
 
<div>
 
<div>

Revision as of 16:13, 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 SOURCE VALUES ('Awl',1,10);
INSERT INTO SOURCE VALUES ('Awl',3,10);
INSERT INTO SOURCE VALUES ('Bowl',2,10);
INSERT INTO SOURCE VALUES ('Bowl',5,10);
INSERT INTO SOURCE VALUES ('Bowl'',6,10);
INSERT INTO source 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