Cannot Perform An Aggregate Function...

[betul]

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.

[betul]

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.

[justsomeguy]

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

[betul]

Thank you, I've tried but I get this error;Msg 102, Level 15, State 1, Line 18Incorrect syntax near 'temp'.

[justsomeguy]

Try removing the quotes around temp.

[betul]

That works perfect. Thank you sooo much.