By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,612 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

Multiple Functions

daniel aristidou
100+
P: 491
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-
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. SELECT        Customer.CCompany, Worker.WName, Sale.SID, Sale.SDate, MAX(SUM((ProductSale.Amount * ProductSale.CRPrice) * ((100 - ProductSale.Discount) / 100))) 
  2.                          AS SOLD
  3. FROM            Sale Left JOIN
  4.                          ProductSale ON Sale.SID = ProductSale.SID Left JOIN
  5.                          Customer ON Sale.CID = Customer.CID Left JOIN
  6.                          Worker ON Sale.WID = Worker.WID
  7. WHERE        (Sale.SDate BETWEEN @StartDate AND @EndDate)
  8. GROUP BY Sale.SID, Sale.WID, Sale.CID, Sale.SDate, Customer.CCompany, Worker.WName
  9. ORDER BY SOLD DESC
However the Sql is returning an error:
---------------------------
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
Jun 12 '09 #1
Share this Question
Share on Google+
3 Replies


Dormilich
Expert Mod 5K+
P: 8,639
maybe you want to go for a stored function?
Jun 12 '09 #2

ck9663
Expert 2.5K+
P: 2,878
Use a subquery instead. Here's the pseudo-code:

Expand|Select|Wrap|Line Numbers
  1. select Customer.CCompany, Worker.WName, Sale.SID, Sale.SDate, max(total_sale) from 
  2. (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
  3. group by Customer.CCompany, Worker.WName, Sale.SID, Sale.SDate
  4.  
That's just a pseudo-code so I hope you know what I'm getting at.

Happy coding!


--- CK
Jun 12 '09 #3

daniel aristidou
100+
P: 491
Thanks CK - That has helped alot :)
Jun 14 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.