I'm using Oracle and SQL Developer. I have downloaded schema Sales History and need to do some queries with it. I'm working with table PRODUCTS. As an user I need the sum of PROD_LIST_PRICE per PROD_CATEGORY, PROD_SUBCATEGORY, SUPPLIER_ID and also the sum per PROD_CATEGORY. I need to provide solution by GROUPING SETS. I do something like this, but seems that it's quite wrong:
WITH ds as(
SELECT prod_category, prod_subcategory, supplier_id, sum(prod_list_price) sum_all,
GROUPING_ID (prod_category, prod_subcategory, supplier_id) group_id
FROM products
GROUP BY
GROUPING SETS (
(prod_category, prod_subcategory, supplier_id), ()
)
)
SELECT decode ( GROUPING (prod_category), 1, 'Total', 0, prod_category) cat,
SUM(prod_list_price) sum_prod
FROM ds
GROUP BY
GROUPING SETS( (prod_category), ())
ORDER BY prod_category;
Maybe I don't understand correctly the conditions. How can I fix this query or modify to do exactly what I need?
CodePudding user response：
It doesn't run in its current form
SQL> WITH ds as(
2 SELECT prod_category, prod_subcategory, supplier_id, sum(prod_list_price) sum_all,
3 GROUPING_ID (prod_category, prod_subcategory, supplier_id) group_id
4 FROM sh.products
5 GROUP BY
6 GROUPING SETS (
7 (prod_category, prod_subcategory, supplier_id), ()
8 )
9 )
10 SELECT decode ( GROUPING (prod_category), 1, 'Total', 0, prod_category) cat,
11 SUM(prod_list_price) sum_prod
12 FROM ds
13 GROUP BY
14 GROUPING SETS( (prod_category), ())
15 ORDER BY prod_category;
SUM(prod_list_price) sum_prod
*
ERROR at line 11:
ORA-00904: "PROD_LIST_PRICE": invalid identifier
but grouping sets is about letting you pick exactly what levels of subtotals you want to do, so its rare to need anything more than a single specification.
If I'm reading your requirements correctly
I need the sum of PROD_LIST_PRICE per PROD_CATEGORY, PROD_SUBCATEGORY, SUPPLIER_ID and also the sum per PROD_CATEGORY
the following should do it
SQL> SELECT
2 prod_category,
3 prod_subcategory,
4 supplier_id,
5 sum(prod_list_price) tot,
6 GROUPING_ID (prod_category, prod_subcategory, supplier_id) group_id
7 FROM sh.products
8 GROUP BY
9 GROUPING SETS (
10 (prod_category, prod_subcategory, supplier_id),
11 (prod_category)
12 );
PROD_CATEGORY PROD_SUBCATEGORY SUPPLIER_ID TOT GROUP_ID
-------------------------------------------------- -------------------------------------------------- ----------- ---------- ----------
Photo Cameras 1 899.99 0
Photo Camcorders 1 1099.99 0
Photo Camera Media 1 155.97 0
Photo Camera Batteries 1 145.95 0
Photo 2301.9 3
Hardware Desktop PCs 1 999.99 0
Hardware Portable PCs 1 1299.99 0
Hardware 2299.98 3
Electronics Home Audio 1 1099.98 0
Electronics Y Box Games 1 152.92 0
Electronics Game Consoles 1 299.99 0
Electronics Y Box Accessories 1 28.98 0
Electronics 1581.87 3
Software/Other Accessories 1 108.95 0
Software/Other Documentation 1 269.94 0
Software/Other Recordable CDs 1 86.93 0
Software/Other Operating Systems 1 199.99 0
Software/Other Bulk Pack Diskettes 1 44.98 0
Software/Other Recordable DVD Discs 1 105.95 0
Software/Other 816.74 3
Peripherals and Accessories CD-ROM 1 254.94 0
Peripherals and Accessories Memory 1 262.98 0
Peripherals and Accessories Monitors 1 1899.98 0
Peripherals and Accessories Modems/Fax 1 90.98 0
Peripherals and Accessories Accessories 1 189.95 0
Peripherals and Accessories Printer Supplies 1 347.96 0
Peripherals and Accessories 3046.79 3