CODEWITHSUNDEEP
sqlsql-server
Charlie
Charlie

Reputation: 1

Summing a Column with Multiple Data Types in SQL

I'm fairly new to SQL and am trying to write a query in sql server to get sums of documents week. My query looks like this:

SELECT 
    [table].[week],
    SUM(ISNULL([table].[documents],0))
FROM 
    [table]
JOIN 
    (VALUES (15,187293),...other pairs...,(127,120918)) AS Outside ([ID],[Organization]) ON Outside.[ID]=[table].[ID]
                                 AND Outside.[Organization] = [table].[Organization]
GROUP BY 
    week

This same query worked on a different table (formatted exactly the same) outputting documents per week, but when I run it on this table, I get the error

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value '#N/A' to data type int.

There is nothing in the documents column that looks like #N/A. How can I make this sum appropriately?

Upvotes: 0

Views: 370

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

To find the offending rows, run the query:

select t.*
from table t
where id = '#N/A' or organization = '#N/A';

Or better yet:

select t.*
from table t
where try_convert(int, id) is null or try_convert(int, organization) is null;

You can fix the problem by using appropriate types in the outside set of values. That is, one or both of the values should be in single quotes so they are strings rather than numbers.

Upvotes: 1

Related Questions