SQL query: NULL values that should not be NULL when using aggregate function with left join

Question

i dont have much expiriance with SQL and i am trying to crack my head on this query. i have 3 tables: Projects, Calculator and partialBilling (note: the 'calculator' columns you see at the code ive added 'k','l','m' etc are real...i didnt gave them those names...).

the query is working fine but part of the values that i am expecting from the aggregate function ('sumofTotal' column) are returning as null values and and they should not be null. I would be grateful if someone point out the mistake in the query.

SELECT Projects.SpCall,Projects.CustName,Projects.CustNumber
,Projects.ReceiveDate,Projects.StartDate,Projects.ProjectType
,Calculator.AN,Projects.Professional,Projects.PmUserName
,Projects.AcountManager,Projects.CrmCallNum,Projects.ProjectCategory
,Projects.CallNum,Projects.ContactName,Projects.ContactPhone
,Projects.ContactEmail,Projects.HiddenNote,Projects.RowColor
, Projects.HeaderCellText,
 SUM(Calculator.K + Calculator.L + Calculator.M + Calculator.N + Calculator.AD + Calculator.AR) AS sumofTotal
 ,partialBilling.Ammount FROM Projects LEFT JOIN Calculator ON Projects.SpCall=Calculator.AQ
  LEFT JOIN partialBilling ON Projects.SpCall = partialBilling.spCall
   WHERE PmUserName= 'JOHN DOE'AND OpertionalStatus
    <> 'Billed' AND OpertionalStatus<> 'Finished' AND
     OpertionalStatus<> 'Passed To Billing' AND OpertionalStatus<> 'Scanning' 
     AND OpertionalStatus<> 'Ended' 
     AND OpertionalStatus<> 'Green Billing' 
     AND (GeneralStatus= 'Passed To Project Manager' 
     OR GeneralStatus= 'Astrategic Project') 
     GROUP BY Projects.SpCall,Projects.CustName,Projects.CustNumber
     ,Projects.ReceiveDate,Projects.StartDate,Projects.ProjectType
     ,Calculator.AN,Projects.Professional,Projects.PmUserName
     ,Projects.AcountManager,Projects.CrmCallNum,Projects.ProjectCategory
     ,Projects.CallNum,Projects.ContactName,Projects.ContactPhone
     ,Projects.ContactEmail,Projects.HiddenNote,Projects.RowColor
     , Projects.HeaderCellText,partialBilling.Ammount;

Simplify the problem and it will be easier for everybody (incl you) to understand it. Much less columns, some sample table data, and the expected result. — jarlh
– jarlh, Commented Apr 5, 2016 at 14:03

dnoeth · Accepted Answer · 2016-04-05 14:25:28Z

1

Instead of proprietary IFNULL better use Standard SQL COALESCE:

SUM(COALESCE(Calculator.K,0) + COALESCE(Calculator.L,0), ...`

Or maybe a bit more efficient:

SUM(COALESCE(Calculator.K,0)) + SUM(COALESCE(Calculator.L,0)), ...`

edited Apr 5, 2016 at 14:25

answered Apr 5, 2016 at 14:14

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

StanislavL · Accepted Answer · 2016-04-05 14:09:31Z

1

Try to use IFNULL()

SUM(IFNULL(Calculator.K,0) + ... + IFNULL(Calculator.AR,0)) AS sumofTotal

answered Apr 5, 2016 at 14:09

StanislavL

57.5k9 gold badges75 silver badges100 bronze badges

Comments

Sam Hanley · Accepted Answer · 2016-04-05 16:50:12Z

0

You can use an expression like ifnull('column_name' , '') in place of column_name.

edited Apr 5, 2016 at 16:50

Sam Hanley

4,7557 gold badges39 silver badges65 bronze badges

answered Apr 5, 2016 at 14:10

dev

4961 gold badge5 silver badges18 bronze badges

Collectives™ on Stack Overflow

SQL query: NULL values that should not be NULL when using aggregate function with left join

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related