Help in building SQL statement

[Mithyrl]

I need some help in building a simple SQL statement:

For example say I have 3 Fields in my database
1) ItemName
2) Quantity
3) Price

What I need to generate is the Total Cost of all of the Items in the Database.

So for example...

ItemName, Quantity, Price
XXXX, 2, .50
YYYY, 4, 1.00
ZZZZ, 1, 2.50
------------------------------------
Total Cost 7.50 < -this is what I need

Total Cost =(quantity*Price) for XXXXX + (quantity*Price) for YYYY + (quantity*Price) for ZZZZ

I am just wondering how to write this as an SQL statement...can anyone help?

[Negative0]

Try this on for size:

SELECT sum(quantity*price) as Total
FROM Table1

[Chris]

Hope this can work, because I haven't try it out.

Dim sql$
Dim Db as DAO.Database
dim Rs as DAO.Recordset

Set Db = DBEngine.Workspaces(0).OpenDatabse(<Your Database>,false, False)
sql = "SELECT DISTINCTROW Sum(Quantity * Price) as MyCost FROM TblName Where Quantity <> 0;"
Set Rs = Db.OpenRecordset(sql,dbOpenSnapshop)
Debug.Print "Total Cost is " & Format(Rs.MyCost,"#,##0.00")

[Mithyrl]

I neglected to mention that I also need the individual ItemName and quantity in the recordset...This is what I have so far...

sql = "SELECT ItemName,Quantity, Sum(Quantity*Price) AS TotalCost
FROM TableName
GROUP BY ItemName;"

TotalCost will give me the TotalCost of each individual Item and not the GrandTotal of all of the items (quantity*Price)
I need the grandtotal of all of the TotalCost(s) for each ItemName. I am unsure of how to do this.

Would I need to use ADO and switch to a SHAPE command to create a multi-tiered query?

[Chris]

I think this should be able to fixed your problem.

Dim sql$
Dim Db as DAO.Database
dim Rs as DAO.Recordset

Set Db = DBEngine.Workspaces(0).OpenDatabse(<Your Database>,false, False)
sql = "SELECT DISTINCTROW ItemName, Sum(Quantity * Price) as MyCost FROM TblName ORDER BY ItemName ASC;"
Set Rs = Db.OpenRecordset(sql,dbOpenSnapshop)
if Rs.RecordCount <>0 Then
   While not Rs.EOF
      Debug.Print Rs.Fields("ItemName") & " Total Cost is " & Format(Rs.MyCost,"#,##0.00")
      Rs.MoveNext
   Wend
End If

[Edited by Chris on 06-09-2000 at 11:10 PM]