Values and Subtotals

From SQLZOO
Revision as of 13:43, 30 July 2012 by Connor (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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