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 trialAllan Oloo
8,054 PointsAdvanced subqueries
-- Generate a report that lists the book titles from both locations and count the total number of books with the same title.
SELECT books_north.title AS title, SUM(books_north.title) AS NumberOfBooks
FROM books_north INNER JOIN books_south
ON books_north.id = books_south.id
GROUP BY books_north.title;
I am curious to know why my implementation does not work? If I am using a inner join to pull data from both table. Calculating the data with sum() function and grouping them by title should work right? I know they want us to use advanced queries, but i was seeing if there was a different way to do the problem .
5 Answers
Steven Parker
231,269 PointsSimon Coates, you're pretty close, but:
- you forgot the
GROUP BY
in your first subquery select - you used books_north twice and did not use books_south at all
But you can simplify the whole thing by returning only titles in the sub-query and counting (and grouping) only in the outer query. This is how I did it:
SELECT title, COUNT(*) as "count"
FROM (SELECT title FROM books_north
UNION ALL
SELECT title FROM books_south)
GROUP BY title
Steven Parker
231,269 PointsAllan Oloo — A few issues stand out for me:
- by using an inner join, you will never see any books that exist in only one location
- the id and location together identify a specific book, but only at that one location
- id's are not guaranteed to refer to the same title (or even exist) at both locations
- joining on id's will therefore mix books with different titles
- the title is not a number and cannot be passed to SUM - did you intend to use COUNT?
Dan Coleman
2,292 PointsYou shouldn't need a don't need a join for this.
The idea is to first create a list of books at both locations first. You can do this with UNION ALL. This forms your subquery.
You then use this subquery to display the title and number of copies using COUNT.
Simon Coates
28,694 Pointsthis is my stab at a solution. I have no clue if it makes any kind of sense:
select TITLE, SUM(count) FROM (
select TITLE, COUNT(title) as count from books_north
UNION ALL
select TITLE, COUNT(title) from books_north GROUP BY TITLE
) AS temp group by title
Stamos Bolovinos
4,320 PointsI had a little different approach. A library typically will have more than one copy of frequently asked books, even in the same location. So in real life there would be books that have maybe 5 copies in books_north, and 3 copies in books_south.
I wanted to display the number of books in each location, in addition to the total number of books.
This is how I did it:
SELECT t1.title 'Title',
IFNULL(t2.'#N',0) '#North',
IFNULL(t3.'#S',0) '#South',
COUNT(*) '#Total'
FROM (
SELECT title FROM books_north
UNION ALL
SELECT title FROM books_south
) t1
LEFT JOIN (
SELECT title,
COUNT(*) '#N'
FROM books_north
GROUP BY title
) t2 ON t1.title = t2.title
LEFT JOIN (
SELECT title,
COUNT(*) '#S'
FROM books_south
GROUP BY title
) t3 ON t1.title = t3.title
GROUP BY t1.title;
Steven Parker
231,269 PointsThis addresses a different objective and does not satisfy the requirements of the code challenge.
But it's an interesting exercise on its own. And you can make the code quite a bit more compact if you rethink your approach a bit.
Stamos Bolovinos
4,320 PointsHi Steven
Thanks for the input! Were you referring to writing something like this?
SELECT t1.title 'Title',
IFNULL(t2.'#N',0) '#North',
COUNT(*) - IFNULL(t2.'#N',0) '#South',
COUNT(*) '#Total'
FROM (
SELECT title FROM books_north
UNION ALL
SELECT title FROM books_south
) t1
LEFT JOIN (
SELECT title,
COUNT(*) '#N'
FROM books_north
GROUP BY title
) t2 ON t1.title = t2.title
GROUP BY t1.title;
Steven Parker
231,269 PointsThat's certainly more compact. I was actually thinking of eliminating the derived table with the unions and just using the JOIN
criteria and filters.
Stamos Bolovinos
4,320 PointsI was also thinking of doing it just with join criteria, but couldn't solve it without using a full outer join, which isn't supported in SQLite. I found the workaround to do a full outer join in SQLite, but it's using again a UNION ALL... and this makes the code bigger than in the previous example. If the full outer join would exist, it would be the solution with less code.
SELECT IFNULL(t3.T1, t3.T2) 'Title',
COUNT(t3.T1) '#North',
COUNT(t3.T2) '#South',
COUNT(t3.T1) + COUNT(t3.T2) '#Total'
FROM (
SELECT t1.title 'T1', t2.title 'T2'
FROM books_north t1
LEFT JOIN books_south t2
ON t1.title = t2.title
UNION ALL
SELECT t1.title, t2.title
FROM books_south t2
LEFT JOIN books_north t1
ON t1.title = t2.title
WHERE t1.title IS NULL
) t3
GROUP BY t3.T1, t3.T2
Steven Parker
231,269 PointsI'd say "good job" if this actually related to the original question.
Simon Coates
28,694 PointsSimon Coates
28,694 Pointsthanks. so much simpler.
Bruce McMinn
10,030 PointsBruce McMinn
10,030 PointsHi Steven, thanks for your input to the community. I'm trying to ad a
WHERE book_num > 1
to this phrase
I'm thinking I need the WHERE phrase one step higher, but not sure. Any hints?
Steven Parker
231,269 PointsSteven Parker
231,269 PointsHigher? But you didn't show where you are placing it.
But since book_num is an aggregate I wouldn't think you could use it in a
WHERE
clause. But you may be able to put it in aHAVING
clause after theGROUP BY
.Bruce McMinn
10,030 PointsBruce McMinn
10,030 PointsRight on, totally worked. I had forgotten about HAVING, I am new to databases. My favorite type of code yet!!
By 'higher' I mean nested further out, how do I say that correctly?
I like what Stamos Bolovinos did, but it's a little more than I've seen yet. So I am trying what Andrew was saying and build the larger block of code from smaller pieces. If I read the question pedantically, it's asking for a list of books and a sum of the books which have more than one copy. A pretty boring result table (one column of titles, one column with the number 12), but it'll be a challenge for me to figure out how to pivot the table and create the equivalency.
Thanks again for your help!
Steven Parker
231,269 PointsSteven Parker
231,269 PointsSure, but next time start a new question instead of asking one in an answer. It will give you a better chance for a rapid reply, and later folks with a similar issue will be able to find your question and answer in a search.