(Using Oracle 8)

I have 4 tables, which I describe below: CUSTOMERS, BOOKS, ORDERS,
ORDERITEMS. These should be pretty self-explanatory, although in case you're
wondering ORDERITEMS constitutes X copies of one book that is part of an
order. Here are the important fields for this problem listings:

CUSTOMERS: CUSTOMER#, LASTNAME, FIRSTNAME

BOOKS: ISBN, RETAIL (company's selling price)

ORDERS: ORDER#, CUSTOMER#

ORDERITEMS: ORDER#, ITEM#, ISBN, QUANTITY

I need some way to calculate the sum of all books in a given order.
Basically, this requires taking (retail * quantity) for every order item in
an order and then adding those values up. I know how to use SUM and all
that, but I am having trouble because of how spread out all these values
are.

Anybody able to help me out? If my description confuses you, I'll be glad to
try to explain it better.

This is a bit simplified based on the information you've provided so
far. You just need a join, a group by, and a sum function.

select orderitems.order#, sum(orderitems.quantity * books.retail)
from orderitems, books
where orderitems.isbn = books.isbn
group by order#

Just two tables. You can join the two others if you need more data
(eg. customer name).

HTH.

"Mark" <nospam (AT) spamfree (DOT) org> wrote