Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / Query Select

 

Getting Values and Subtotals in One Go with UNION

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.