Optimize Query with SUM() and CASE... statements

[Jacob Drew]

Hi--

I need some help optimizing the following query. basically, i'm using aggregate functions to tabulate stats. summary tables are not a great option for me, because there are a number of different 'filters' users can select that change the WHERE clause.

What's the best way to optimize these aggregate functions? And what's the best way to optimize using CASE... For instance, it doesn't seem to help to have an index on colType1, though I assumed it would given the CASE statements.

THANKS!


SELECT
COUNT(games) as totGames,

AVG(CASE
WHEN colType1 = 'var' THEN (colAmt1/constant)*100
WHEN colType2 = 'var' THEN (colAmt2/constant)*100
WHEN colType3 = 'var' THEN (colAmt3/constant)*100
END) as avgColPercentRelativeToConstant,

AVG(CASE
WHEN colType1 = 'calls' THEN colAmt1
WHEN colType2 = 'calls' THEN colAmt2
WHEN colType3 = 'calls' THEN colAmt3
END) as avgColAmt,

SUM(
CASE WHEN colType1 = 'var2'
OR colType2 = 'var2'
OR colType3 = 'var2'
THEN 1 ELSE 0 END) as totCols_asVar2

FROM tbl_a

WHERE
randomCol_1 = userInput_2
AND
randomCol_2 = userInput_2