I am trying to aggregate a subset colB (an nvarchar) by filtering out all non-convertible values from colB. I’m using a CTE along the lines of:

;with cte as (   select     colA,     colB   from Tbl )  select   cte.colA,   sum(convert(int, cte.colB)) from cte where cte.colA in (...) group by cte.colA 

The filtering on colA guarantees that all the values in colB are convertible to ints. But the aggregation errors…

I get this message:

Conversion failed when converting the nvarchar value ‘Yes’ to data type int.

Without the grouping there is no ‘Yes’ value in the result set. All of the result set are numbers.

So I guess that the aggregation is happening before the where filter is being applied. How can I get around this?

A link to a gist with the actual query is available here: https://gist.githubusercontent.com/zachsa/f38b08357ad4841a8e776db9226ef66d/raw/071f17ab0079d370e1aec2b70e1f976dc74fd1ea/Aggregating%2520and%2520filtering