We help IT Professionals succeed at work.

Add 2 to the sum of a SQL statement

Sheldon Livingston
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

Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
What about
SELECT SUM(strDebit)+2 AS theSum FROM ...

Open in new window

Sheldon LivingstonConsultant

Author

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 ...

Open in new window

What db are you working with?
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

What database and version?

Sheldon LivingstonConsultant

Author

Commented:
Thank you Daniel.

So in MS SQL server why doesn't

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

work?
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
In MySQL you could do

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

Open in new window

Sheldon LivingstonConsultant

Author

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

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

Open in new window

Sheldon LivingstonConsultant

Author

Commented:
Yea... tried that Daniel
Sheldon LivingstonConsultant

Author

Commented:
No joy
Sheldon LivingstonConsultant

Author

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