perez@csc.ti.com (Edward Perez) (02/21/90)
i've read thru Oracle's SQL*Plus Reference Guide Ver 2.0 several times to find
an answer to the following but no luck. any help from you db-netters ??
i'm writing several queries to calculate statistics about some data in our SQL
database. what i want to do it calculate the stats once and then print them
out several ways.
example (tables and syntax are for conceptual presentation only)
Table1
item-no in-stock num-items-used warehouse-id
1 5 2 1
2 10 3 2
3 18 0 3
4 33 0 1
5 13 0 3
6 24 0 1
(essentialy # items
on parts list)
Table2
item-no seq-no item-used
1 1 2
1 2 6
2 1 3
2 1 4
2 1 5
assume space_cost = 5 handling cost = 10
query 1 : print number of items, total in stock, total cost for stocking each
item, cost for just storage & handling, total number of items used grouped by
warehouse and item number.
sql: select
count(item_no),
sum(in_stock),
sum(in_stock)*space_cost+sum(in_stock)*handling_cost,
sum(in_stock)*space_cost,
sum(in_stock)*handling_cost,
sum(num_items_used),
warehouse_id
from table1
group by warehouse_id, item_no order by warehouse_id, item_no;
answer: warehouse 1 - 3 items, 62 in stock, space=310, handling=620
total cost=930, total items on parts list=2
warehouse 2 - 1 item, 10 in stock, space=50, handling=100,
total cost=150, total items on parts list=3
warehouse 3 - 2 items, 31 in stock, space=155, handling=310,
total cost=465, total items on parts list=0
query 2 : print number of items, total in stock, total cost for stocking each
item, cost for just storage & handling, total number of items used for ALL
warehouses.
sql: select
count(item_no),
sum(in_stock),
sum(in_stock)*space_cost+sum(in_stock)*handling_cost,
sum(in_stock)*space_cost,
sum(in_stock)*handling_cost,
sum(num_items_used)
from table1;
answer: warehouse 1 - 6 items, 103 in stock, space=515, handling=1030,
total cost=1545, total items on parts list = 5
query 3: print mininum, average, maximum number of items in stock.
sql: select
count(item_no),
min(in_stock),
max(in_stock),
avg(in_stock),
warehouse_id
from table1
group by warehouse_id, item_no order by warehouse_id, item_no;
thus, i would like to compute the number of items, number of items in stock,
space and handling cost, etc. once and then do the computations without having
to recompute these things each time. can this be done ?? would the Oracle
report generator be the better tool to use ??
---------------------------------------------------------------------
edward roland perez internet: perez@csc.ti.com #
computer science center csnet: perez%ti-csl@relay.cs.net #
phone: 214-995-0698(w) #
---------------------------------------------------------------------