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 trialPhilip Porwisch
13,229 PointsQuery 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
231,184 PointsMath 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 |
Philip Porwisch
13,229 PointsPhilip Porwisch
13,229 PointsThank you !