error with SQL statement

hi, i try the below sql code, however when i try to execute the command, it always give me the error 'Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.'

If i remove the "(SELECT [description] = CASE WHEN ([description]) <> '' THEN [description] ELSE [tbl_batch_completed].[status] END)" under the group by clause, it will work

do I need to change something in the sql statement?

Thanks in advance

SELECT TAG_FACE_CON.REQUEST_ID,
(SELECT [description] = CASE WHEN ([description]) <> '' THEN [description] ELSE [tbl_batch_completed].[status] END)AS status
FROM (TAG_FACE_CON RIGHT JOIN tbl_batch_completed ON TAG_FACE_CON.GROUP_ID = tbl_batch_completed.ID)
GROUP BY TAG_FACE_CON.REQUEST_ID,(SELECT [description] = CASE WHEN ([description]) <> '' THEN [description] ELSE [tbl_batch_completed].[status] END)

You are going to have to remove the select staement from the group by. Also in that SQl Staement there is no from clause... It does not know where to get the batch completed information.

I would look into create a temp table to create the first section as raw data, then when you need to do the group by run it as a query from the temp table.|||I think restructuring your query like this will solve your problem:

SELECT
 TAG_FACE_CON.REQUEST_ID,
 CASE
 WHEN [description] <> '' THEN [description]
 ELSE [tbl_batch_completed].[status]
 END AS status
FROM
 TAG_FACE_CON
RIGHT JOIN
 tbl_batch_completed ON TAG_FACE_CON.GROUP_ID = tbl_batch_completed.ID
GROUP BY
 TAG_FACE_CON.REQUEST_ID,
 CASE
 WHEN [description] <> '' THEN [description]
 ELSE [tbl_batch_completed].[status]
 END

Terri