SUM and COUNT BIGINT values in SQL Server

To sum and count bigint values in sql server we might not able to use the traditional functions (SUM and COUNT) as they are. Let us see the problem and how to solve it.

--create temp table for our test
if object_id('tempdb.sys.#table1') is not null
	drop table #table1;
create table #table1(
	col1 int
);
 
--insert test values
insert into #table1 (col1) 
	values(2147483647),
	      (2147483647);
 
--total value of col1
select sum(col1) col1Total
from #table1;

As you can see we get error “Arithmetic overflow error converting expression to data type int.”

--solution is simply cast the column to bigint
select  sum(cast(col1 as bigint))
from #table1;

Now let us try to count a table that has the maximum number of records on int data type(2147483647).

--let us prepare the table 
insert into #table1 (col1) 
	select 1;
go 2200000000 --this will execute the batch 2200000000 times

This time to get the total number of records count() function will no longer work for us.

SELECT COUNT_BIG(*) [Total Records]
FROM #table1;