Getting Values and Subtotals in One Go with GROUPING SETS : GROUPING « Analytical Functions « Oracle PL/SQL Tutorial

SQL>
SQL> CREATE TABLE product
  2  (item         VARCHAR(10)
  3  ,serialNumber INT
  4  ,price        INT
  5  );

Table created.

SQL> INSERT INTO product VALUES ('AAA', 1,10);

1 row created.

SQL> INSERT INTO product VALUES ('BBB',2,10);

1 row created.

SQL> INSERT INTO product VALUES ('AAA', 3,10);

1 row created.

SQL> INSERT INTO product VALUES ('CCC',4,10);

1 row created.

SQL> INSERT INTO product VALUES ('BBB',5,10);

1 row created.

SQL> INSERT INTO product VALUES ('BBB',6,10);

1 row created.

SQL>
SQL> SELECT item, serialNumber, SUM(price)
  2    FROM product
  3    GROUP BY GROUPING SETS ((item,serialNumber),(item),());

ITEM       SERIALNUMBER SUM(PRICE)
---------- ------------ ----------
AAA                   1         10
AAA                   3         10
AAA                             20
BBB                   2         10
BBB                   5         10
BBB                   6         10
BBB                             30
CCC                   4         10
CCC                             10
                                60

10 rows selected.

SQL>
SQL> DROP TABLE product;

Table dropped.

SQL>

16.13.GROUPING
	16.13.1.	GROUPING() with a Single Column in a ROLLUP
	16.13.2.	Using DECODE() to Convert the Returned Value from GROUPING()
	16.13.3.	Using DECODE() and GROUPING() to Convert Multiple Column Values
	16.13.4.	Using GROUPING() with CUBE
	16.13.5.	Using the GROUPING SETS Clause
	16.13.6.	Computing the GROUPING Bit Vector
	16.13.7.	Using the GROUP_ID() Function
	16.13.8.	Eliminate duplicate rows using a HAVING clause that only allows rows whose GROUP_ID() is 0
	16.13.9.	Grouping with Rollup and Cube
	16.13.10.	Use GROUPING(x) function in a DECODE or CASE to enhance the result
	16.13.11.	Use the BREAK reporting tool to space the display conveniently
	16.13.12.	Getting Values and Subtotals in One Go with GROUPING SETS