SQL statement

[ivalum21]

Here is my SQL statement:

SELECT OrderTbl.OrdNo, OrdDate, OrdName, (ProdPrice * Qty) AS TotalAmount
FROM OrderTbl, Customer, OrdLine, Product
WHERE OrderTbl.OrdNo = OrdLine.OrdNo
AND Product.ProdNo = OrdLine.ProdNo
AND Customer.CustNo = OrderTbl.CustNo
AND OrdDate = #1/23/2004#

I need it to take the OrdNo (order #'s) and output the total amount by multiplying the price types the quantity ordered (TotalAmount). Right now it displays each individual order instead of combining them. If one order includes a $15 item and a $10 item, it displays the two items seperately instead of display the one order number with a total of $25.

What am I doing wrong?

 

[webmigit]

SELECT OrderTbl.OrdNo, OrdDate, OrdName, SUM(ProdPrice * Qty) AS TotalAmount
FROM OrderTbl, Customer, OrdLine, Product
WHERE OrderTbl.OrdNo = OrdLine.OrdNo
AND Product.ProdNo = OrdLine.ProdNo
AND Customer.CustNo = OrderTbl.CustNo
AND OrdDate = #1/23/2004#
GROUP BY OrderTbl.OrdNo, OrdDate, OrdName

Unfortunately (I've been through this same thing before.. or really similar). For this to work, OrdDate, and Ordname have to be the same... great if they are.. but if they arent...

They way SUM works, in order to use it, you have to basically list all fields that are not in it but are selected in the group by clause. If your sql statement contains an Aggregate Function (Sum(), Max(), Min(), Avg() and some others) or a group by clause, it will probably need to contain the other.

If OrdDate or OrdName is not the same.... do you have to have them selected? I know its conventient.

If you have to include the date... maybe this will work for you...

SELECT OrderTbl.OrdNo, Max(OrdDate) as MaxODate, Min(OrdDate) as MinODate, OrdName, SUM(ProdPrice * Qty) AS TotalAmount
FROM OrderTbl, Customer, OrdLine, Product
WHERE OrderTbl.OrdNo = OrdLine.OrdNo
AND Product.ProdNo = OrdLine.ProdNo
AND Customer.CustNo = OrderTbl.CustNo
AND OrdDate = #1/23/2004#
Order by OrderTbl.OrdNo
GROUP BY OrderTbl.OrdNo, OrdDate, OrdName

In my similar situation.. I settled for that... I said "[Items] between #MinODate# and #MaxODate#."

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.