|
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, price FROM product
2 UNION
3 SELECT item, NULL, SUM(price)
4 FROM product
5 GROUP BY item
6 ORDER BY item;
ITEM SERIALNUMBER 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
9 rows selected.
SQL> SELECT item, serialNumber, price FROM(
2 SELECT item, serialNumber, price FROM product
3 UNION
4 SELECT item, NULL, SUM(price)
5 FROM product
6 GROUP BY item
7 UNION
8 SELECT NULL, NULL, SUM(price)
9 FROM product
10 ) t
11 ORDER BY COALESCE(item,'zzz'), COALESCE(serialNumber,1E9);
ITEM SERIALNUMBER 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.
|