looking to tune query...

If anyone is able to provide advice for tuning the below query in sql
server, it is much appreciated. In addition any index suggestions are
also appreciated as I have access to the tables. Thank you.

select a.id, isnull(b.advisement_satisfaction_yes, 0) as
advisement_satisfaction_yes,
isnull(c.advisement_satisfaction_no, 0) as advisement_satisfaction_no,
case
when isnull(b.advisement_satisfaction_yes, 0) >
isnull(c.advisement_satisfaction_no, 0) then 'YES'
when isnull(b.advisement_satisfaction_yes, 0) <
isnull(c.advisement_satisfaction_no, 0) then 'NO'
when isnull(b.advisement_satisfaction_yes, 0) =
isnull(c.advisement_satisfaction_no, 0) then 'TIE'
end as Satisfied_With_Advisement

from a
left Join

(select id, count(answer_text) as Advisement_Satisfaction_yes from a
where question = 'The level of Academic Advisement I received from the
University staff during this course was appropriate.'
and answer_text = 'yes'
GROUP BY id) b

on a.id = b.id

left join

(select id, count(answer_text) as Advisement_Satisfaction_NO from a
where question = 'The level of Academic Advisement I received from the
University staff during this course was appropriate.'
and answer_text = 'NO'
GROUP BY id) c

on a.id = b.id

where question = 'The level of Academic Advisement I received from the
University staff during this course was appropriate.'The above query should provide the same results as your query with a
DISTINCT clause, but is shorter and should perform better:

SELECT id, Advisement_Satisfaction_yes, Advisement_Satisfaction_NO,
CASE
WHEN Advisement_Satisfaction_yes>Advisement_Satisfaction_NO
THEN 'YES'
WHEN Advisement_Satisfaction_yes<Advisement_Satisfaction_NO
THEN 'NO'
ELSE 'TIE'
END AS Satisfied_With_Advisement
FROM (
SELECT id,
SUM(CASE WHEN answer_text='yes' THEN 1 ELSE 0 END)
AS Advisement_Satisfaction_yes,
SUM(CASE WHEN answer_text='NO' THEN 1 ELSE 0 END)
AS Advisement_Satisfaction_NO
FROM a GROUP BY id
) x

Razvan|||AMAZING...THANK YOU SO MUCH!

Razvan Socol wrote:

Quote:

Originally Posted by

The above query should provide the same results as your query with a
DISTINCT clause, but is shorter and should perform better:
>
SELECT id, Advisement_Satisfaction_yes, Advisement_Satisfaction_NO,
CASE
WHEN Advisement_Satisfaction_yes>Advisement_Satisfaction_NO
THEN 'YES'
WHEN Advisement_Satisfaction_yes<Advisement_Satisfaction_NO
THEN 'NO'
ELSE 'TIE'
END AS Satisfied_With_Advisement
FROM (
SELECT id,
SUM(CASE WHEN answer_text='yes' THEN 1 ELSE 0 END)
AS Advisement_Satisfaction_yes,
SUM(CASE WHEN answer_text='NO' THEN 1 ELSE 0 END)
AS Advisement_Satisfaction_NO
FROM a GROUP BY id
) x
>
Razvan