SUM(Case... ) condition tuning
674344Aug 14 2009 — edited Aug 14 2009Hi All I have a query in which lot of SUM(Case...) statements and is taking a lot of time to run...
I have mentioned a smaller version of the query....
SELECT test1_units, test_unit2,
SUM(CASE WHEN tran_code = 1 THEN units ELSE 0 END) AS mtd_sales_units,
SUM(CASE WHEN tran_code = 1 THEN total_cost ELSE 0 END) AS mtd_sales_cost,
SUM(CASE WHEN tran_code = 1 THEN total_retail ELSE 0 END) AS mtd_sales_ret,
from table_test
In this we can see that all the SUM(Case....) are having same condition :tran_code = '1' .
Is there any way we can use these under one conditional statement Which mean one condition with all three cases.... any other tuning suggestion are also welcome