SQL within an SQL??

[doctrin13th]

I have tables:

1. trans_tbl (transaction table)

• trans_date = date of transaction
• trans_no = transaction number
• trans_cash = cash tendered
• trans_change = change (cash tendered - total amount due*)

*total amount due is computed in the next table

2. sold_tbl (items sold table)

• trans_no = foreign key which points to the transaction (trans_tbl) where sold item belongs
• sold_itemID = foreign key which points to the item account (item_tbl) where item description is extracted
• sold_price = current price of the item sold
• sold_qty = qty sold

How can I generate a summary of sales report? For example, I want to generate a report that lists the total sales from all transaction dating from March 1, 2013 to March 5, 2013.

to compute the trans_change, I have to sum the "price * qty" of all items in a single transaction (that is the total amount due), then I have to compute (trans_cash - total amount due)

the following is an example of the output

Date | *Amount Due | *Cash | *Change
================================
3/1/13 | 200.00 | 300.00 | 100.00
3/2/13 | 500.00 | 250.00 | 250.00
3/3/13 | 100.00 | 300.00 | 200.00
3/4/13 | 300.00 | 400.00 | 100.00
3/5/13 | 500.00 | 200.00 | 300.00

* totals of all transactions in that date

How can i obtain that output? I have no code yet because I have no idea?
I'm aware, and I can do it using two recordset objects and using looping but I want to do it using aggregate functions and computations within the SQL statement, using a single recordset, which I believe is faster.

I'm thinking SQL within SQL. The total amount due computation (price * qty) maybe inside the aggregate SUM??? is it possible?

[Hack]

Moved To Database Development

[ColinE66]

Air code but it will be something like this...

SELECT trans_date AS Date
SUM(sold_price * sold_qty) AS AmountDue,
SUM(trans_cash) AS Cash,
SUM(trans_cash - (sold_price * sold_qty)) AS Change,
FROM trans_tbl t,
sold_tbl s
WHERE s.trans_no = t.trans_no
GROUP BY Date