Hi. I get an error saying that 'A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations' when I try to select a column (that i need for a group by) that's in the same select where a value is assigned to a variable - here it is:

declare @acty FLOAT

SELECT @acty = eval_id,
sum(cast(ques.answer_value AS INTEGER))
FROM QUESTION_DATA AS ques, VALID_ANSWERS AS ans, #ytd_eval1a
WHERE ques.score_value_ind = 'S'
and ques.tx_id = #ytd_eval1a.tx_id
and ques.code = ans.code
and ques.answer_value = ans.answer_value
and ans.valid_answer <> 'NA'
group by eval_id

I need the group by eval_id so that the SUM above is broken out by eval_id.

How else can I get the value of @acty broken out by eval_id? Any help is greatly appreciated

If I understand what you are doing, you are trying to get the sum of the answer values for each eval_id. Your error message is telling you that you can not select @variable = value from table at the same time as you select count(value) from table. If you are only going to have 1 eval_id you could select @acty = eval_id, @sum = xxxx from xxxx. Another aproach might be: remove your variable assignment and select the result set into another table (probably a temp) and then select the sum(values) out of that table based on the eval_id. That way you get the whole result set out of your query and then parse through it assigning each value to a variable.

Thanks very much for your suggestion.

I figured that one out as:

SELECT sum(cast(ques.answer_value AS INTEGER)) AS actytd, eval_id
FROM QUESTION_DATA AS ques, VALID_ANSWERS AS ans, #ytd_eval1a
WHERE ques.score_value_ind = 'S'
and ques.tx_id = #ytd_eval1a.tx_id
and ques.code = ans.code
and ques.answer_value = ans.answer_value
and ans.valid_answer <> 'NA'
GROUP BY eval_id
I was able to eliminate the value assignment.

But now I'm stuck on the next query that has to be broken out by eval_id because it has subselects. I am already using 2 temp tables so I'm hesitating to use any more.

What's your take on grouping the following by eval_id?

SELECT sum(q.maxValues)
FROM
(
SELECT DISTINCT code, MAX(answer_value) AS maxValues
FROM VALID_ANSWERS
WHERE VALID_ANSWERS.code in
(
SELECT ques.code
FROM QUESTION_DATA AS ques, VALID_ANSWERS AS ans, #ytd_eval1a
WHERE ques.score_value_ind = 'S'
and ques.tx_id = #ytd_eval1a.tx_id
and ques.code = ans.code
and ques.answer_value = ans.answer_value
and ans.valid_answer <> 'NA')
GROUP BY code
)
AS q

thank you very much for any and all help!!!!!! I'm pulling my hair out on this one.

Not really sure on that one, you may want to try rewriting your query to avoid the subselects. That might give you a fresh look on it and it is more effecient for sure.

SELECT DISTINCT code, MAX(answer_value) AS maxValues
FROM VALID_ANSWERS ans
JOIN QUESTION_DATA ques
ON ques.code = ans.code
AND ques.answer_value = ans.answer_value
JOIN #ytd_eval1a
ON ques.tx_id = #ytd_eval1a.tx_id
WHERE ques.score_value_ind = 'S'
AND ans.valid_answer <> 'NA')
GROUP BY code

Good luck,
Ryan