I am trying to aggregate a subset of values from
colB (nvarchar) by filtering out all non-convertible values from this column first. 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 results in an error...
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