1 Reply Latest reply on Mar 27, 2013 11:51 AM by russell.spangler

    Converting Nulls to zero (0) when using left join and group by?

    J G

      I currently have a table list of IDs and several transactional tables that have these IDs as well as other IDs for this field. A one to many relationship combined by a left join between my table in the other tables. Because I want to sum and combine multiple fields from the transactional tables into one dashboard I am using the 'group by' in the left join. Looking like this:

       

      SELECT [trans$].[ID] AS [ID],

      [trans$].[Spend] AS [Spend],

      [trans$].[col1] AS [col1],

      [trans$].[col2] AS [col2I],

      FROM [trans$]

      LEFT JOIN ['primarytable$']

      ON ['primarytable$'].[ID] = [trans$].[ID]

      GROUP BY [trans$].[ID] AS [ID],

      [trans$].[Spend]

      [trans$].[col1],

      [trans$].[col2]

       

      I need to sum from the multiple transaction tables on the  ['primarytable$'].[ID] list. However [trans$].[ID] does not have all the ID's that PrimaryTable has. So when I make the join, the ID does not exist in Trans then I would like Spend to be 0, not null. I ask because  [trans$].[Spend]is in several equations which will default to Null if  [trans$].[Spend] is Null. I would prefer for it to be 0 so the equations still work.

       

      Is anyone able to help?

       

      Thank you ahead of time