Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

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.


 

 

��  
 
 

 
 
 
 
oracle dba poster
 

 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.