These IDs seem like they should be a table as, there are several queries that use the same list of IDs.  The following needs to be refactored.SELECT SUM(CASE WHEN s.TypeID IN(1,2,3,4,7,8,10) THEN 1 ELSE 0 END) FROM Sales sThe problem is a subquery can't be used in an aggregate function.  I'd like to write it as follows, but it causes an error:SELECT SUM(CASE WHEN s.TypeID IN(SELECT ID FROM SaleTypes WHERE SaleType.Desc = 'US') THEN 1 ELSE 0 END) FROM Sales s
Rewriting SQL IN statement with long list of parameters
sql;sql server
I'm assuming the poster pasted just the relevant snippet and this is merely one column of many.  For that purpose, I would prefer an outer join.SELECT SUM(CASE WHEN st.ID IS NOT NULL THEN 1 ELSE 0 END)  FROM Sales s  LEFT JOIN SaleTypes st         ON (s.TypeId=st.ID AND st.Desc='US')