Values and Subtotals

Get values and Subtotals in one shot.

<p>Table 1 shows the result without the subtotals and Table 2 shows the result with subtotals.

Table 1
whn	description	amount
2006-11-01	Wages	50
2006-11-02	Company Store	-10
2006-11-03	Company Store	-10
2006-11-04	Company Store	-10
2006-11-05	Company Store	-10
2006-11-06	Company Store	-10

Table 2
whn	description	amount
2006-11-01	Wages	50
2006-11-02	Company Store	-10
2006-11-03	Company Store	-10
2006-11-04	Company Store	-10
2006-11-05	Company Store	-10
2006-11-06	Company Store	-10

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)

Grand total can also be obtained through ROLLUPS:

SELECT item, serialnumber, SUM(price) FROM serial

GROUP BY item, serialnumber WITH ROLLUP

Grand total can also be obtained through ROLLUPS:

SELECT item, serialnumber, SUM(price) FROM serial

GROUP BY item, serialnumber WITH ROLLUP

Grand total can be obtained through GROUPING SETS:

SELECT item, serialnumber, SUM(price) FROM serial

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

Values and Subtotals

Personal tools

Namespaces

Variants

Views

Actions

Search

Reference

Toolbox

Google AdSense