Rewriting SQL IN statement with long list of parameters 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 s


The 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
Inside your SUM, each record has a value of either 1 or 0. The value is 1 if some condition is true for that record, and 0 if false. In other words, you're really counting the records where the condition is true.

The condition appears to be, Sales.TypeID should be one of 1,2,3,4,7,8,10. And if I understood it correctly, these correspond to the ids of the SaleType records where SaleType.Desc = 'US'.

It sounds like you want to join the Sales and SaleTypes tables,
where Sales.TypeID match SaleTypes.ID,
and SaleType.Desc = 'US'.
So this query:

SELECT count(*) 
FROM Sales s 
JOIN SaleTypes st 
ON s.TypeID = st.ID 
WHERE SaleTypes.Desc = 'US'