SQL Sum problem with joins

Michael Graham
Michael Graham used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

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 Analyst

Author

Commented:
Thanks for your help.

However, I cannot use that syntax with the version of SQL Sever I am using (2012).
Most Valuable Expert 2012
Distinguished Expert 2018

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)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

awking00Information Technology Specialist

Commented:
Try coalesce -
sum(coalesce(ECB.ResponseTotalAmountPaid,0)+coalesce(LPC.ResponsePatientPayAmount,0)) as Total
SharathData Engineer

Commented:
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 Analyst

Author

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

Thanks
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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 Specialist

Commented:
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 Specialist

Commented:
>>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
Most Valuable Expert 2012
Distinguished Expert 2018

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 Analyst

Author

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
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
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
Michael GrahamData Analyst

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial