SQL Sum problem with joins

Hello,

I am having a problem with SQL when I use a sum.  

Below is the Syntax that gets the correct sum:

Select
      
      dbo.fnEatonType_CHC(ECB.ClaimID) as PayerType,
      sum(LPC.UsualAndCustomary) as 'U&C',
      sum(ECB.ResponseTotalAmountPaid) as Total,
      sum(LPC.ResponsePatientPayAmount) as PatientPay
From
      vw_Eaton_ClaimsBase ECB left outer join vw_Reporting_LastPaidClaim LPC on LPC.ClaimID = ECB.ClaimID


However,

if I I do:

Select
      
      dbo.fnEatonType_CHC(ECB.ClaimID) as PayerType,
      sum(LPC.UsualAndCustomary) as 'U&C',
      sum(ECB.ResponseTotalAmountPaid+LPC.ResponsePatientPayAmount) as Total
From
      vw_Eaton_ClaimsBase ECB left outer join vw_Reporting_LastPaidClaim LPC on LPC.ClaimID = ECB.ClaimID

the second sum (Total) is incorrect (it lowers the ECB.ResponseTotalAmountPaid considerably).

I know it has to do with the join - is there any way to add them together in one sum rather than two sums?

Thanks for any help
Michael GrahamData AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Not a SQL Server person but in other databases, if you add a null the result is null.

See if this helps:
 sum(nullif(ECB.ResponseTotalAmountPaid,0)+nullif(LPC.ResponsePatientPayAmount,0)) as Total
Michael GrahamData AnalystAuthor Commented:
Thanks for your help.

However, I cannot use that syntax with the version of SQL Sever I am using (2012).
slightwv (䄆 Netminder) Commented:
nullif has been around since SQL Server 2008

What isn't working?


https://docs.microsoft.com/en-us/sql/t-sql/language-elements/nullif-transact-sql?view=sql-server-2017
APPLIES TO: SQL Server (starting with 2008)
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

awking00Information Technology SpecialistCommented:
Try coalesce -
sum(coalesce(ECB.ResponseTotalAmountPaid,0)+coalesce(LPC.ResponsePatientPayAmount,0)) as Total
SharathData EngineerCommented:
try like this.
Select 
      
      dbo.fnEatonType_CHC(ECB.ClaimID) as PayerType,
      sum(LPC.UsualAndCustomary) as 'U&C',
      sum(ECB.ResponseTotalAmountPaid+ISNULL(LPC.ResponsePatientPayAmount,0) as Total
From
      vw_Eaton_ClaimsBase ECB left outer join vw_Reporting_LastPaidClaim LPC on LPC.ClaimID = ECB.ClaimID

Open in new window

Michael GrahamData AnalystAuthor Commented:
IT says LNullIf is not a recognized built in function name.

Thanks
Scott PletcherSenior DBACommented:
Most often it's best when possible to do the SUMs before you join, something like below.  Otherwise it can be tricky from keeping the JOIN(s) from decreasing/increasing the sums.

Select      
      dbo.fnEatonType_CHC(ECB.ClaimID) as PayerType,
      LPC.UsualAndCustomary as 'U&C',
      ECB.ResponseTotalAmountPaid+LPC.ResponsePatientPayAmount as Total
From
      (Select
             ClaimID,
             sum(ResponseTotalAmountPaid) as ResponseTotalAmountPaid
       From vw_Eaton_ClaimsBase
       Group By ClaimID
      ) as ECB left outer join
      (Select
             ClaimID,
             sum(UsualAndCustomary) as UsualAndCustomary,
             sum(ResponsePatientPayAmount) as ResponsePatientPayAmount
       From vw_Reporting_LastPaidClaim
       Group By ClaimID
      ) as LPC on LPC.ClaimID = ECB.ClaimID
awking00Information Technology SpecialistCommented:
sigtwv,
Nullif should have also worked. If the asker copied and pasted your suggestion, it may have failed because of the nullif showing up between the L and the PC and not before.
awking00Information Technology SpecialistCommented:
>>IT says LNullIf is not a recognized built in function name.<<
As I suspected, it was meant to say Nullif(LPC and not LNullif(PC
slightwv (䄆 Netminder) Commented:
>>t may have failed because of the nullif showing up between the L and the PC and not before.

Ah yes, typos!!!

I have corrected it.
Michael GrahamData AnalystAuthor Commented:
Hi Scott,

Your solutions seems to be running the best.  

It is my fault I forgot to include that I need to put in the where clause:

ECB.DateFilled >= '2017-01-01'
      and ECB.DateFilled <= '2018-01-01'
      and ECB.InventoryName = 'Codman'
      and ECB.PlanName not like '%Stock%'

Is there any way to build that into the subquery?

Thanks,

Mike
Scott PletcherSenior DBACommented:
Sure.  Just put it in the "ECB" subquery:


Select      
      dbo.fnEatonType_CHC(ECB.ClaimID) as PayerType,
      LPC.UsualAndCustomary as 'U&C',
      ECB.ResponseTotalAmountPaid+LPC.ResponsePatientPayAmount as Total
From
      (Select
             ClaimID,
             sum(ResponseTotalAmountPaid) as ResponseTotalAmountPaid
       From vw_Eaton_ClaimsBase
       Where DateFilled >= '2017-01-01'
            and DateFilled < '2018-01-01' /*I changed to < because <= just didn't seem correct*/
            and InventoryName = 'Codman'
            and PlanName not like '%Stock%'
       Group By ClaimID
      ) as ECB left outer join
      (Select
             ClaimID,
             sum(UsualAndCustomary) as UsualAndCustomary,
             sum(ResponsePatientPayAmount) as ResponsePatientPayAmount
       From vw_Reporting_LastPaidClaim
       Group By ClaimID
      ) as LPC on LPC.ClaimID = ECB.ClaimID

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Michael GrahamData AnalystAuthor Commented:
I tested both of the queries.  The one from Scott and the the original from Slight both worked out.

I am not great at doing these credits - you guys all helped and I learned something new with the ISNULL function.

Thank you guys so much.

Mike
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.