looking to tune query...

[blvandeb]

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.'

[Razvan Socol]

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_Satisfactio n_NO
THEN 'YES'
WHEN Advisement_Satisfaction_yes<Advisement_Satisfactio n_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

[blvandeb]

AMAZING...THANK YOU SO MUCH!

Razvan Socol wrote:

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_Satisfactio n_NO
THEN 'YES'
WHEN Advisement_Satisfaction_yes<Advisement_Satisfactio n_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