| 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
|