Multiple Functions

[daniel aristidou]

hi guys what i am trying to do with my query is to for all of the workers in the database calculate their sales and return only their personal best. (within a certain time period)

so there are two basic steps calculate the sale total-

SUM((ProductSale.Amount * ProductSale.CRPrice) * ((100 - ProductSale.Discount) / 100))

but then only return the Max Value calculated for each worker.
So i assume i need a Max Function on this calculation. thus giving me

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

However the Sql is returning an error:
---------------------------
Microsoft Visual Studio
---------------------------
SQL Execution Error.

Executed SQL statement: SELECT Customer.CCompa ny, Worker.WName, Sale.SID, Sale.SDate, MAX(SUM((Produc tSale.Amount * ProductSale.CRP rice) * ((100 - ProductSale.Dis count) / 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

[Dormilich]

maybe you want to go for a stored function?

[ck9663]

Use a subquery instead. Here's the pseudo-code:

select Customer.CCompany, Worker.WName, Sale.SID, Sale.SDate, max(total_sale) from 
(select Customer.CCompany, Worker.WName, Sale.SID, Sale.SDate, sum(sale1+ sale2 + blah + blah) from yourtable JOIN anothertable on key1 = key2 group by Customer.CCompany, Worker.WName, Sale.SID, Sale.SDate) subquery
group by Customer.CCompany, Worker.WName, Sale.SID, Sale.SDate

That's just a pseudo-code so I hope you know what I'm getting at.

Happy coding!


--- CK

[daniel aristidou]

Thanks CK - That has helped alot :)