Aggregates containing subqueries...

So I already no this can't be done... but I need a suitable alternative (if one exists) so I don't have to competely re-write this already too huge query.

Anyways, in my select I have something like this:

sum( case when code in (1,2,3,4) then 0 else 1 end ) as total

which has now increase from four static values to a more dynamic format, that being a table with 47 values which may shrink or grow over time. Attempting the following fails:

sum( case when code in (select code_id from ExcludedCodes) then 0 else 1 end ) as total

because apparently you can't use selects or aggregates within an aggregate.

So any ideas on how I can get this working... is there no Array or List type variable I could just substitute in? I've already tried using a Table Variable, but that failed as well.

Please keep in mind, this is one line of a very large select containing many other fields and aggregates (on a fair amount of joins) which is used in at least four differerent reporting queries. If there is no quick and easy replacement trick I can do just let me know so I can start re-writing all of them (which is seriously going to make my head hurt).

Try this

SELECT COUNT(t1.code) AS TOTAL FROM TABLE1 t1 WHERE t1.code NOT IN (SELECT t2.code FROM TABLE2 t2)

|||

Do a LEFT JOIN with the table ExcludedCodes and then use CASE to return 0 or 1 appropriately. Then you can SUM around the CASE.

|||

The left join works perfectly, I'm not sure why I didn't think of that earlier. Just set my case to check if current_code.code_id = exclude_code.code_id then 0 else 1 and the rest of the query didn't have to change at all thanks.

|||

Well sure, use the solution from the guy who has 150 times as many points as me