Including SUM Totals With 0 or NULL values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Including SUM Totals With 0 or NULL values

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2015-02-06 : 13:32:29

Hello, my code below works well for finding the SUM total when the balance is over $0 but how do I include those values where TotalBalance = $0 or there are no matching records? Thanks

SELECT C.cnsmr_idntfr_lgcy_txt AS SSN, SUM(CABBR.cnsmr_accnt_bckt_crrnt_bal_amnt) AS TotalBalance
FROM cnsmr AS C INNER JOIN
cnsmr_accnt AS CA ON C.cnsmr_id = CA.cnsmr_id INNER JOIN
cnsmr_accnt_bckt_bal_rprtng AS CABBR ON CA.cnsmr_accnt_id = CABBR.cnsmr_accnt_id INNER JOIN
adhoc_reports ON C.cnsmr_idntfr_lgcy_txt = adhoc_reports.SSN
GROUP BY C.cnsmr_idntfr_lgcy_txt
ORDER BY SSN

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-02-06 : 13:42:59

The "standard" way to do that would be to to change the INNER JOINs to LEFT OUTER JOINs:


SELECT C.cnsmr_idntfr_lgcy_txt AS SSN, SUM(CABBR.cnsmr_accnt_bckt_crrnt_bal_amnt) AS TotalBalance
FROM cnsmr AS C LEFT OUTER JOIN
 cnsmr_accnt AS CA ON C.cnsmr_id = CA.cnsmr_id LEFT OUTER JOIN
 cnsmr_accnt_bckt_bal_rprtng AS CABBR ON CA.cnsmr_accnt_id = CABBR.cnsmr_accnt_id LEFT OUTER JOIN
 adhoc_reports ON C.cnsmr_idntfr_lgcy_txt = adhoc_reports.SSN
GROUP BY C.cnsmr_idntfr_lgcy_txt
ORDER BY SSN

evanburen
Posting Yak Master

167 Posts

Posted - 2015-02-06 : 14:38:08

Thanks, Scott.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources