Let's say that I want to have a query like the following:

SELECT Field1, Field2,
CASE Field3
WHEN 1 THEN 'GOOD'
WHEN 2 THEN 'BAD'
ELSE 'UGLY'
END AS FieldResult,
SUM(Field4) AS TotalField4
FROM Table1
GROUP BY Field1, Field2, FieldResult

How do I accomplish something like this? The query above does not work
as it does not recognize a field called FieldResult.

I got it to work by changing the query as shown below, but I am
wondering if there is a smarter/better way to do it.

SELECT Field1, Field2,
CASE Field3
WHEN 1 THEN 'GOOD'
WHEN 2 THEN 'BAD'
ELSE 'UGLY'
END AS FieldResult,
SUM(Field4) AS TotalField4
FROM Table1
GROUP BY Field1, Field2, CASE Field3
WHEN 1 THEN 'GOOD'
WHEN 2 THEN 'BAD'
ELSE 'UGLY'
END

you can try selecting from a result set, do all your manipulation via
CASE in the inner result set,, and save the SUM till you have the data
as you need it. then do the SUM and GROUP on the outer part.

SELECT Field1, Field2, FieldResult , SUM(Field4)
FROM(
SELECT Field1, Field2,
CASE Field3
WHEN 1 THEN 'GOOD'
WHEN 2 THEN 'BAD'
ELSE 'UGLY'
END AS FieldResult,
-- SUM(Field4) AS TotalField4
FROM Table1
) AS A
GROUP BY Field1, Field2, FieldResult