so there are two basic steps calculate the sale total-
Expand|Select|Wrap|Line Numbers
- SUM((ProductSale.Amount * ProductSale.CRPrice) * ((100 - ProductSale.Discount) / 100))
So i assume i need a Max Function on this calculation. thus giving me
Expand|Select|Wrap|Line Numbers
- SELECT Customer.CCompany, Worker.WName, Sale.SID, Sale.SDate, MAX(SUM((ProductSale.Amount * ProductSale.CRPrice) * ((100 - ProductSale.Discount) / 100)))
- AS SOLD
- FROM Sale Left JOIN
- ProductSale ON Sale.SID = ProductSale.SID Left JOIN
- Customer ON Sale.CID = Customer.CID Left JOIN
- Worker ON Sale.WID = Worker.WID
- WHERE (Sale.SDate BETWEEN @StartDate AND @EndDate)
- GROUP BY Sale.SID, Sale.WID, Sale.CID, Sale.SDate, Customer.CCompany, Worker.WName
- ORDER BY SOLD DESC
---------------------------
Microsoft Visual Studio
---------------------------
SQL Execution Error.
Executed SQL statement: SELECT Customer.CCompany, Worker.WName, Sale.SID, Sale.SDate, MAX(SUM((ProductSale.Amount * ProductSale.CRPrice) * ((100 - ProductSale.Discount) / 100))) AS SOLD FROM Sale LEFT OUTER JOIN ProductSale ON Sale.SID = ProductSale.SID LEFT OUTER JOIN Cust...
Error Source: .Net SqlClient Data Provider
Error Message: Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
---------------------------
---------------------------
Any help with this is greatly appreciated