Arithmetic overflow error converting expression to data type int?

Question

Getting an error on line 1 of this code...

Select COUNT(MEM_REL) As MemberSub, CLM_CC2 As GroupNum
From Impact.dbo.tbl_mem, Impact.dbo.tbl_clm 
Where MEM_REL = '01'
Group by CLM_CC2

The error is:

MSg 8815, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int

becuase you are passing MEM_REL = '01' you have to pass MEM_REL = 1 — wiretext
You really should not use a comma separated list of tables. It works but it it a very old join style. You should be explicit and state that you want a CROSS JOIN. sqlblog.com/blogs/aaron_bertrand/archive/2009/10/08/… — Sean Lange

M.Ali M.Ali · Accepted Answer · 2015-08-20T16:17:59

Try introducing a proper relation to the query, right now you are getting a Cartesian product of the two tables. For example instead of the comma , use the key word JOIN to join two tables and then define the relation on what column you want to join these tables.

Once you have done the above the number of rows returned will dramatically decrease and the following query may work, if you have more than 2.2 billion rows in the following query then you may need to use COUNT_BIG() function instead of just COUNT()

Select COUNT(MEM_REL) As MemberSub
     , CLM_CC2 As GroupNum
From Impact.dbo.tbl_mem
INNER JOIN Impact.dbo.tbl_clm 
ON dbo.tbl_mem.[ReferencingColumn] = dbo.tbl_clm.[ReferencingColumn]
Where MEM_REL = '01'
Group by CLM_CC2

Query with COUNT_BIG()

Select COUNT_BIG(MEM_REL) As MemberSub
     , CLM_CC2 As GroupNum
From Impact.dbo.tbl_mem
INNER JOIN Impact.dbo.tbl_clm 
ON dbo.tbl_mem.[ReferencingColumn] = dbo.tbl_clm.[ReferencingColumn]
Where MEM_REL = '01'
Group by CLM_CC2

Arithmetic overflow error converting expression to data type int?

1 Answers