We help IT Professionals succeed at work.

# Add 2 to the sum of a SQL statement

on
I want:

"select sum(strDebit) as theSum from ..."

to always return 2 more than theSum

So, if theSum = 5, the field would contain a 7.

Doable?
Comment
Watch Question

## View Solution Only

President / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
``````SELECT SUM(strDebit)+2 AS theSum FROM ...
``````
Consultant

Commented:
I was hoping for a default answer of 2. But it appears that if the where clause causes no records to be returned then "theSum" doesn't exist.

Does that sound right? Is there are work around so that I always get an answer of at least 2?
President / Owner CARDA Consultants Inc.
Distinguished Expert 2018
Commented:
It depends on the db in question, but say in Access you could do
``````SELECT Nz(SUM(strDebit),0)+2 AS theSum FROM ...
``````
What db are you working with?
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

What database and version?

Consultant

Commented:
Thank you Daniel.

So in MS SQL server why doesn't

select sum(isnull(d.fquantity,0)) + 2 as theTotal2

work?
President / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
In MySQL you could do

``````SELECT COALESCE(SUM(strDebit),0)+2 AS theSum FROM ...
``````
Consultant

Commented:
SQL server 2016
President / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
For SQL Server Try
``````SELECT ISNULL(SUM(strDebit),0)+2 AS theSum FROM ...
``````
Consultant

Commented:
Yea... tried that Daniel
Consultant

Commented:
No joy
Consultant

Commented:
Complete code

select ISNULL(SUM(d.fquantity),0)+2 as theTotal2
from M2MDATA01.dbo.somast AS c
left outer join M2MDATA01.dbo.soitem AS d on c.fsono = d.fsono
where c.fstatus = 'Open'
and (fgroup in ('SS'))
and (charindex('45MIN',fdescmemo) = 0 and charindex('60MIN',fdescmemo) = 0 and charindex('90MIN',fdescmemo) = 0)

I get "Item cannot be found in the collection corresponding to the requested name or ordinal." when displaying theTotal2