Values and Subtotals

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)

Grand total can also be obtained through ROLLUPS:

SELECT item, serialnumber, SUM(price) FROM source

GROUP BY item, serialnumber WITH ROLLUP

Grand total can also be obtained through ROLLUPS:

SELECT item, serialnumber, SUM(price) FROM source

GROUP BY item, serialnumber WITH ROLLUP

Grand total can be obtained through GROUPING SETS:

SELECT item, serialnumber, SUM(price) FROM source

GROUP BY GROUPING SETS ((item,serialnumber),(item),())

Values and Subtotals

Personal tools

Namespaces

Variants

Views

Actions

Search

Reference

Toolbox

Google AdSense