Make your own free website on Tripod.com
 
Home Page

Back to Papers
 
 

Introduction

Definitions

Basic Usage

Logic-dependent Aggregation

An Interlude

Logic-dependent Aggregation (contd)

Pivot Tables and Multi-part Logic

Beyond Equality

Conclusion

Additional Resources

Please note this page has moved to http://oracledoug.com/case4.html. You will be redirected in 5 seconds but it would be better to update your bookmarks ;-)

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.

Previous

Next

Technical Papers Utilities and Scripts Book Reviews Links
My ResumeFun & GamesEmailHome