| |
 |
|
Oracle sum function
Oracle Tips by Burleson Consulting |
Your task is to write an SQL
query against the pubs database that display each book store, a
list of the titles sold at each book store, and the sum of all
copies sold for each title.
The report will look like this:
Store Book
Books
Name Title
Sold
------------------------- ------------------------------
--------
Barnes And Noble Windows Sucks
1,000
Reduce Spending The Republican
900
Way
The Willow Weeps No More
180
Oracle9i Sql Tuning
100
Blue
Ride Booksellers Managing Stress
3,400
The Fall Of Microsoft
800
Unix For Experts
600
Dos For Dummies
100
Non Violins In The Workplace
100
Pay No Taxes And Go To Jail
100
Writers Market
100
Reduce Spending The Republican
100
Way
Bears Are People Too
100
Books
For Dummies Windows Sucks
8,000
The Fall Of Microsoft
2,100
Piano Greats
800
Pay No Taxes And Go To Jail
700
Non Violins In The Workplace
500
Bears Are People Too
300
Writers Market
300
Unix For Experts
100
Oracle9i Sql Tuning
100
Managing Stress
100
Borders Dos For Dummies
8,900
The Willow Weeps No More
8,800
The Fall Of Microsoft
1,400
Writers Market
1,400
Managing Stress
600
Bears Are People Too
500
Reduce Spending The Republican
150
Way
Oracle9i Sql Tuning
100
Piano Greats
10
Eaton
Books Pay No Taxes And Go To Jail
10,000
Piano Greats
1,020
Oracle9i Sql Tuning
600
The Zen Of Auto Repair
100
Unix For Experts
100
Bears Are People Too
100
Non Violins In The Workplace
100
Operations Research Theory
100
Hot
Wet And Sexy Books Piano Greats
6,100
Pay No Taxes And Go To Jail
5,500
Writers Market
5,500
The Zen Of Auto Repair
3,700
Dos For Dummies
1,400
Zero Loss Finance
800
Unix For Experts
500
Non Violins In The Workplace
500
Operations Research Theory
500
The Fall Of Microsoft
200
Ignoramus And Dufus Piano Greats
2,060
Zero Loss Finance
850
Windows Sucks
300
Dos For Dummies
200
The Zen Of Auto Repair
100
Operations Research Theory
100
Quagmire Books Managing Stress
5,100
Dos For Dummies
800
Bears Are People Too
800
Reduce Spending The Republican
600
Way
Piano Greats
400
Windows Sucks
100
Writers Market
100
Specialty Bookstore Piano Greats
5,500
Dos For Dummies
180
Oracle9i Sql Tuning
100
The Willow Weeps No More
100
Reduce Spending The Republican
100
Way
Pay No Taxes And Go To Jail
100
Wee
Bee Books Windows Sucks 8,800
Writers Market
1,000
Reduce Spending The Republican
900
Way
Operations Research Theory
800
The Willow Weeps No More
800
Zero Loss Finance
600
Piano Greats
400
The Zen Of Auto Repair
100
Unix For Experts
100
Oracle9i Sql Tuning
100
Non Violins In The Workplace
100
80
rows selected.
Hint: remember to use the sum BIF to summarize the data and to GROUP BY all non-summed
columns.
Part 2
Modify your report to also show
the total dollar sales and sum the dollar sales when to store
name changes.
Hint: Add a column to the select statement to
multiply the total units sold by the book retail price.
The revised report will
look like this:
Store Book
Books Dollar
Name Title
Sold Sales
------------------------- ------------------------------
-------- ------------
Barnes And Noble Windows Sucks
1,000 $34,950.00
Reduce Spending The Republican
900 $25,155.00
Way
The Willow Weeps No More
180 $5,391.00
Oracle9i Sql Tuning
100 $4,995.00
*************************
------------
sum
$70,491.00
Blue Ride Booksellers Managing Stress
3,400 $135,830.00
The Fall Of Microsoft
800 $15,960.00
Unix For Experts
600 $23,370.00
Dos For Dummies
100 $1,995.00
Non Violins In The Workplace
100 $1,195.00
Pay No Taxes And Go To Jail
100 $1,095.00
Writers Market
100 $2,295.00
Reduce Spending The Republican
100 $2,795.00
Way
Bears Are People Too
100 $3,495.00
*************************
------------
sum
$188,030.00
Books For Dummies Windows Sucks
8,000 $279,600.00
The Fall Of Microsoft
2,100 $41,895.00
Piano Greats
800 $26,360.00
Pay No Taxes And Go To Jail
700 $7,665.00
Non Violins In The Workplace
500 $5,975.00
Bears Are People Too
300 $10,485.00
Writers Market
300 $6,885.00
Unix For Experts
100 $3,895.00
Oracle9i Sql Tuning
100 $4,995.00
Managing Stress
100 $3,995.00
*************************
------------
sum
$391,750.00
Borders Dos For Dummies
8,900 $177,555.00
The Willow Weeps No More
8,800 $263,560.00
The Fall Of Microsoft
1,400 $27,930.00
Writers Market
1,400 $32,130.00
Managing Stress
600 $23,970.00
Bears Are People Too
500 $17,475.00
Reduce Spending The Republican
150 $4,192.50
Way
Oracle9i Sql Tuning
100 $4,995.00
Piano Greats
10 $329.50
*************************
------------
sum
$552,137.00
Eaton Books Pay No Taxes And Go To Jail
10,000 $109,500.00
Piano Greats
1,020 $33,609.00
Oracle9i Sql Tuning
600 $29,970.00
The Zen Of Auto Repair
100 $9,995.00
Unix For Experts
100 $3,895.00
Bears Are People Too
100 $3,495.00
Non Violins In The Workplace
100 $1,195.00
Operations Research Theory
100 $4,495.00
*************************
------------
sum
$196,154.00
Hot Wet And Sexy Books Piano Greats
6,100 $200,995.00
Pay No Taxes And Go To Jail
5,500 $60,225.00
Writers Market
5,500 $126,225.00
The Zen Of Auto Repair
3,700 $369,815.00
Dos For Dummies
1,400 $27,930.00
Zero Loss Finance
800 $17,560.00
Unix For Experts
500 $19,475.00
Non Violins In The Workplace
500 $5,975.00
Operations Research Theory
500 $22,475.00
The Fall Of Microsoft
200 $3,990.00
*************************
------------
sum
$854,665.00
Ignoramus And Dufus Piano Greats
2,060 $67,877.00
Zero Loss Finance
850 $18,657.50
Windows Sucks
300 $10,485.00
Dos For Dummies
200 $3,990.00
The Zen Of Auto Repair
100 $9,995.00
Operations Research Theory
100 $4,495.00
*************************
------------
sum
$115,499.50
Quagmire Books Managing Stress
5,100 $203,745.00
Dos For Dummies
800 $15,960.00
Bears Are People Too
800 $27,960.00
Reduce Spending The Republican
600 $16,770.00
Way
Piano Greats
400 $13,180.00
Windows Sucks
100 $3,495.00
Writers Market
100 $2,295.00
*************************
------------
sum
$283,405.00
Specialty Bookstore Piano Greats
5,500 $181,225.00
Dos For Dummies
180 $3,591.00
Oracle9i Sql Tuning
100 $4,995.00
The Willow Weeps No More
100 $2,995.00
Reduce Spending The Republican
100 $2,795.00
Way
Pay No Taxes And Go To Jail
100 $1,095.00
*************************
------------
sum
$196,696.00
Wee Bee Books Windows Sucks
8,800 $307,560.00
Writers Market
1,000 $22,950.00
Reduce Spending The Republican
900 $25,155.00
Way
Operations Research Theory
800 $35,960.00
The Willow Weeps No More
800 $23,960.00
Zero Loss Finance
600 $13,170.00
Piano Greats
400 $13,180.00
The Zen Of Auto Repair
100 $9,995.00
Unix For Experts
100 $3,895.00
Oracle9i Sql Tuning
100 $4,995.00
Non Violins In The Workplace
100 $1,195.00
*************************
------------
sum
$462,015.00
ANSWERS
Part 1
set
pages 999
col
c1 heading 'Store|Name' format a25
col
c2 heading 'Book|Title' format a30
col
c3 heading 'Books|Sold' format 999,999
break
on c1 skip 2
select
initcap(store_name) c1,
initcap(book_title) c2,
sum(quantity) c3
from
store,
sales,
book
where
store.store_key = sales.store_key
and
sales.book_key = book.book_key
group
by
store_name,
book_title,
book_retail_price
order
by
store_name,
c3
desc
;
Part 2
set
pages 999
col
c1 heading 'Store|Name' format a25
col
c2 heading 'Book|Title' format a30
col
c3 heading 'Books|Sold' format 999,999
col
c4 heading 'Total|Dollar|Sales' format $999,999.99
break
on c1 skip 2
compute sum of c4 on c1
select
initcap(store_name) c1,
initcap(book_title) c2,
sum(quantity) c3,
sum(quantity)*book_retail_price c4
from
store,
sales,
book
where
store.store_key = sales.store_key
and
sales.book_key = book.book_key
group
by
store_name,
book_title,
book_retail_price
order
by
store_name,
c3
desc
;
 |
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
|
|
|
