|
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(
2 SELECT item, serialNumber, price FROM product
3 UNION
4 SELECT item, NULL, SUM(price)
5 FROM product
6 GROUP BY item
7 ) t
8 ORDER BY item, 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
9 rows selected.
SQL>
SQL> DROP TABLE product;
Table dropped.
SQL>
|