Need help formatting a SELECT query (w/subqueries?)

I have a database for a shopping cart program, and I want to create a page that my boss can access to see which products have been purchased. The query should produce output like this:

Product Code | Product Name | Quantity Sold | Total Value
—————————————————–

LAP-123 | White Envelopes | 203 | $265.02

Orders are stored in two tables: Orders, which creates an ID per order and stores details like the total order amount, and Order_Details which links the productid’s ordered and the number of each product ordered with the orderid from Orders.

There’s also a Products table, which has productid’s, product name and product codes.

There’s also a Pricing table, which is where the prices for each product are stored.

I have the basic query down already, I can make it print all the products that have been ordered. What I need it to do is combine all the occurances of each product together and then total the quantity sold and the combined value sold.

Here’s what I have:

[code=php]SELECT o.productid,o.product_options, o.amount,
prod.productid, prod.productcode,
prod.product FROM xcart_order_details AS
o, xcart_products AS prod WHERE
o.productid = prod.productid[/code]

What that does is print out the product code, the product name/product options, and the number sold, but there are repeating instances of products that have been ordered more than once. I suspect I need the GROUP BY modifier, but I don’t understand how to do that. I also need to get the Pricing table involved somehow, I think.

Can anybody help?

Also in #PHP _↴