Re: Need help with an SQL query

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.org> wrote in message news:<Vg%Bc.93770$Sw.27476_at_attbi_s51>...
> (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.
Received on Wed Jun 23 2004 - 04:59:29 CEST