Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases Querying Relational Databases Subqueries Review and Practice

Query not returning correct total

Regarding the second practice (User loans), I wrote a query (see below) that shows for any patron the open loans in the south and north + a total of both.

This functions perfectly fine for all patrons BUT patron 4. Patron 4 does have a value in the OpenLoansS column but a NULL in the OpenLoansN column. The TotalOpenLoans column becomes NULL. Could someone please help me figure out why?

QUERY (feel free to copy+paste):

SELECT p.id, p.first_name, NRsouth.OpenLoans AS OpenLoansS, NRnorth.OpenLoans AS OpenLoansN, NRsouth.OpenLoans + NRnorth.OpenLoans AS TotalOpenLoans FROM patrons AS p LEFT OUTER JOIN ( SELECT patron_id, COUNT() AS OpenLoans FROM loans_south WHERE returned_on IS NULL GROUP BY patron_id ) AS NRsouth ON NRsouth.patron_id = p.id LEFT OUTER JOIN ( SELECT patron_id, COUNT() AS OpenLoans FROM loans_north WHERE returned_on IS NULL GROUP BY patron_id ) AS NRnorth ON NRnorth.patron_id = p.id GROUP BY p.id;

RETURNED SET:

id / first_name / OpenLoansS / OpenLoansN / TotalOpenLoans;

1 / Andrew / 6 / 4 / 10

2 / Dave / 2 / 5 / 7

3 / Alena / 4 / 4 / 8

4 / Michael / 3 / NULL / NULL

5 / Craig / NULL / NULL / NULL

1 Answer

Steven Parker
Steven Parker
231,265 Points

Math with "null" always produces "null", but you can convert nulls into 0 using the COALESCE function:

COALESCE(NRsouth.OpenLoans, 0) + COALESCE(NRnorth.OpenLoans, 0) AS TotalOpenLoans 

This gives:

id first_name OpenLoansS OpenLoansN TotalOpenLoans
1 Andrew 6 4 10
2 Dave 2 5 7
3 Alena 4 4 8
4 Michael 3 3
5 Craig 0

Thank you !