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 (
  from Tbl

  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

  • if you are using SQL Server 2016, you can use TRY_CONVERT: sum(try_convert(int,cte.ColB)) – Lamak Jan 31 '18 at 15:43
  • 1
    See this answer for an explanation of why this happens: stackoverflow.com/questions/5191701/… and the (previously known as connect item): bug report: SQL Server should not raise illogical errors – ypercubeᵀᴹ Jan 31 '18 at 16:02
  • Thanks @Lamak. I have to maintain compatibility with SQL Server 2012, but try_convert seams to work on that version as well, and that solved my problem – Zach Smith Jan 31 '18 at 20:27
  • 1
    Why do you need a CTE when it is just a straight select? – paparazzo Jan 31 '18 at 21:05
  • It is a select now. initially i wrote it with not in where clause requiring a re-joining of the initial select - so i would have had to have written the join statements twice. I'm not sure what the difference in terms of execution is – Zach Smith Feb 5 '18 at 12:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.