Jump to content

Cannot Perform An Aggregate Function...


betul
 Share

Recommended Posts

Hi all,I have a procedure as below;

ALTER PROCEDURE [dbo].[GetActivePeriod]AS SELECT  w.WorkKeeper, SUM (DATEDIFF (ss, a.ProcessDate, MIN(b.ProcessDate) ) ) AS 'ActivePeriod' FROM Work w, ProcessLog a INNER JOIN ProcessLog b ON (b.ProcessId > a.ProcessId AND b.WorkId=a.WorkId)WHERE CONVERT( varchar(10), a.ProcessDate, 111) = CONVERT( varchar(10), GETDATE(), 111)AND CONVERT( varchar(10), b.ProcessDate, 111) = CONVERT( varchar(10), GETDATE(), 111)AND a.ProcessNum=1AND ( b.ProcessNum=2 OR b.ProcessNum=4 OR b.ProcessNum=5 OR b.ProcessNum=6 )AND w.WorkId=a.WorkIdGROUP BY w.WorkKeeper

And I get this error, "Cannot perform an aggregate function on an expression containing an aggregate or a subquery." When I remove MIN, it sums all the rows that matches the criteria :) , but I only want the one with min ProcessDate. Could you please help me?Thanks.

Link to comment
Share on other sites

Ok, I've changed the procedure like this;

ALTER PROCEDURE [dbo].[GetActivePeriod]ASselect w.WorkKeeper, DATEDIFF(ss,a.ProcessDate, MIN(b.ProcessDate)) AS 'ActivationPeriod'FROM Work w ,ProcessLog a  INNER JOIN ProcessLog bON (a.ProcessId < b.ProcessId)WHERE CONVERT(varchar(10),a.ProcessDate,111) = CONVERT(varchar(10),GETDATE(),111) AND a.ProcessName=1AND CONVERT(varchar(10),b.ProcessDate,111) =  CONVERT(varchar(10),GETDATE(),111) AND (b.ProcessNum=2 OR b.ProcessNum=4 OR b.ProcessNum=5 OR b.ProcessNum=6) AND a.WorkId=w.WorkId AND b.WorkId=w.WorkId GROUP BY w.WorkKeeper,a.ProcessDate

And the result is ;

WorkKeeper	  Activation Period   3				   80  3				   304  3				   117  6				   39  6				   30

But what I want is to sum the Activation Period for the same WorkKeepers. So the result would be;

WorkKeeper	  Activation Period  3					 501  6					 69

How can I do this? Any help/idea is appreciated.

Link to comment
Share on other sites

You may be able to wrap everything you have in another group by:

SELECT WorkKeeper, SUM(ActivationPeriod) AS 'ActivationPeriod' FROM (select w.WorkKeeper, DATEDIFF(ss,a.ProcessDate, MIN(b.ProcessDate)) AS 'ActivationPeriod'FROM Work w ,ProcessLog a  INNER JOIN ProcessLog bON (a.ProcessId < b.ProcessId)WHERE CONVERT(varchar(10),a.ProcessDate,111) = CONVERT(varchar(10),GETDATE(),111) AND a.ProcessName=1AND CONVERT(varchar(10),b.ProcessDate,111) =  CONVERT(varchar(10),GETDATE(),111) AND (b.ProcessNum=2 OR b.ProcessNum=4 OR b.ProcessNum=5 OR b.ProcessNum=6) AND a.WorkId=w.WorkId AND b.WorkId=w.WorkId GROUP BY w.WorkKeeper,a.ProcessDate) AS 'temp'GROUP BY WorkKeeper

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...