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
Alexandros Panayi
1,771 PointsScaling result horizontally for books
For the first practice problem, my solution is: select count(*) as 'total books', title from (select title from books_north union all select title from books_south) as a group by title
The only issue is that this stacks all the books vertically. How can we scale this horizontally, so we can show "books_north.title" and "books_south.title" as two different columns, instead of one title column? I know the issue is with the union
Alexandros Panayi
1,771 PointsWhy would books from books_north and books from books_south be the exact same? If it is, why have two tables?
The whole point is to show two cols, one is titles from books_north and the other is titles from books_south, so my question is how to achieve this
Alexandros Panayi
1,771 PointsIt's the first question in the "Subqueries Practice" from the "Querying Relational Database" course.
1 Answer
Steven Parker
231,269 PointsI think I found it, and here's a link to it ... the actual chapter name is Review and Practice.
So the instructions say "Generate a report that lists the book titles from both locations and count the total number of books with the same title."
The two tables have the book inventory from each of two locations (north and south). But the way I would interpret those instructions is that the report should have only one title on each line, with a total number of books stored at both locations for that title.
So based on that assessment, you wouldn't want a plain UNION in the subquery since it would add a uniqueness constraint that would affect the count. But UNION ALL would give you a complete set. Then, you might want to use a GROUP BY to combine the items by title so you can COUNT them.
It might look something like this:
SELECT title as "Book Title", COUNT(1) as Count FROM
(SELECT title FROM books_north UNION ALL SELECT title FROM books_south)
GROUP BY title
Alexandros Panayi
1,771 PointsHey Steven, thanks for the response.
That is my original answer.
However I want to know how to scale this horizontally, so instead of having all the titles from both tables under one col, I would like to show each title from each table, along with the count.
Steven Parker
231,269 PointsOh, do you mean something like this?
SELECT title as "Book Title", location, COUNT(1) as Count FROM
(SELECT title, "North" as Location FROM books_north
UNION ALL SELECT title, "South" FROM books_south)
GROUP BY title, location
If that's not it, show me what a few lines of what the output you expect to get would look like.
Steven Parker
231,269 PointsSteven Parker
231,269 PointsThe whole point of the exercise is to show each book title on a separate line. Why would you want to show titles in two columns if they are always going to be the same?
You might be having a different issue. Can you provide a link to the challenge page itself?