Logic-Dependent Aggregation

Imagine a situation where the Sales Manager requests a report to examine the effect on 2001 (calendar year) revenue of applying a 10% mark-up on Photo-related products. The report needs to give the total revenue for each product category and subcategory. This entails calculating the total of the sales.amount_sold column for all products, which is straightforward using GROUP BY and SUM as shown in Example 2a.

Example 2a

SET AUTOTRACE ON REM First a few SQL*Plus formatting commands SET PAGES 999 SET LINES 160 COLUMN prod_category FORMAT A30 COLUMN prod_subcategory FORMAT A26 COLUMN dollars FORMAT 999,999,990.90 BREAK ON prod_category SKIP 1 COMPUTE SUM OF dollars ON prod_category REM Now the query SELECT p.prod_category, p.prod_subcategory, sum(s.amount_sold) AS dollars FROM sales s, times t, products p WHERE s.time_id = t.time_id AND s.prod_id = p.prod_id AND t.calendar_year = 2001 GROUP BY p.prod_category, p.prod_subcategory ORDER BY p.prod_category, p.prod_subcategory; PROD_CATEGORY PROD_SUBCATEGORY DOLLARS ------------------------------ -------------------------- --------------- Electronics Game Consoles 1,205,027.35 Home Audio 2,779,398.57 Y Box Accessories 161,004.00 Y Box Games 559,421.03 ****************************** --------------- sum 4,704,850.95 Hardware Desktop PCs 2,230,713.39 Portable PCs 3,453,656.62 ****************************** --------------- sum 5,684,370.01 Peripherals and Accessories Accessories 663,034.82 CD-ROM 669,134.90 Memory 1,228,555.41 Modems/Fax 874,702.07 Monitors 3,191,525.93 Printer Supplies 1,232,754.58 ****************************** --------------- sum 7,859,707.71 Photo Camcorders 2,819,074.98 Camera Batteries 757,626.90 Camera Media 551,090.37 Cameras 2,205,836.66 ****************************** --------------- sum 6,333,628.91 Software/Other Accessories 521,342.80 Bulk Pack Diskettes 88,216.04 Documentation 827,932.29 Operating Systems 1,020,370.87 Recordable CDs 367,478.04 Recordable DVD Discs 728,564.36 ****************************** --------------- sum 3,553,904.40


Returning a different value for Photo products adds a little complication. There are several possible solutions. We could use two different copies of the sales table in the FROM clause, or we could use a UNION of two complementary data sets, Photo and non-Photo products, as shown in Example 2b.

(Note - at this stage, I'll enable the SQL*Plus AUTOTRACE facility to expose the execution plans of the various approaches to the problem. If you haven't used this before, you can find more information HERE)

Example 2b

SELECT p.prod_category, p.prod_subcategory, SUM(s.amount_sold) * 1.1 AS dollars FROM sales s, times t, products p WHERE s.time_id = t.time_id AND s.prod_id = p.prod_id AND t.calendar_year = 2001 AND p.prod_category = 'Photo' GROUP BY p.prod_category, p.prod_subcategory UNION ALL SELECT p.prod_category, p.prod_subcategory, sum(s.amount_sold) AS dollars FROM sales s, times t, products p WHERE s.time_id = t.time_id AND s.prod_id = p.prod_id AND t.calendar_year = 2001 AND p.prod_category != 'Photo' GROUP BY p.prod_category, p.prod_subcategory ORDER BY 1, 2; PROD_CATEGORY PROD_SUBCATEGORY DOLLARS ------------------------------ -------------------------- --------------- Electronics Game Consoles 1,205,027.35 Home Audio 2,779,398.57 Y Box Accessories 161,004.00 Y Box Games 559,421.03 ****************************** --------------- sum 4,704,850.95 Hardware Desktop PCs 2,230,713.39 Portable PCs 3,453,656.62 ****************************** --------------- sum 5,684,370.01 Peripherals and Accessories Accessories 663,034.82 CD-ROM 669,134.90 Memory 1,228,555.41 Modems/Fax 874,702.07 Monitors 3,191,525.93 Printer Supplies 1,232,754.58 ****************************** --------------- sum 7,859,707.71 Photo Camcorders 3,100,982.48 Camera Batteries 833,389.59 Camera Media 606,199.41 Cameras 2,426,420.33 ****************************** --------------- sum 6,966,991.80 Software/Other Accessories 521,342.80 Bulk Pack Diskettes 88,216.04 Documentation 827,932.29 Operating Systems 1,020,370.87 Recordable CDs 367,478.04 Recordable DVD Discs 728,564.36 ****************************** --------------- sum 3,553,904.40

Although this statement will produce the desired results it will perform two full table scans against the sales table to return the complementary data sets which are then UNIONed. (Note that I've used UNION ALL because we know that the two data sets are already complementary.) So the output shown in bold text comes from the first query block, before the UNION ALL, and the rest comes from the second query block.

Next

Improving SQL efficiency using CASE