Values and Subtotals

From SQLZOO
Revision as of 14:21, 2 August 2012 by Connor (Talk | contribs)

Jump to: navigation, search

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