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

Allan Oloo
Allan Oloo
8,054 Points
Posted by Allan Oloo
Allan Oloo
8,054 Points

Advanced 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
Steven Parker
231,269 Points
Steven Parker
Steven Parker
231,269 Points

Simon 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
Simon Coates
Simon Coates
28,694 Points
Simon Coates
Simon Coates
28,694 Points

thanks. so much simpler.

Bruce McMinn
Bruce McMinn
10,030 Points
Bruce McMinn
Bruce McMinn
10,030 Points

Hi Steven, thanks for your input to the community. I'm trying to ad a 

WHERE book_num > 1

to this phrase

SELECT title, COUNT(*) as book_num
FROM (SELECT title FROM books_north 
      UNION ALL
      SELECT title FROM books_south)
GROUP BY title
ORDER BY book_num DESC;

I'm thinking I need the WHERE phrase one step higher, but not sure. Any hints?

Steven Parker
Steven Parker
231,269 Points
Steven Parker
Steven Parker
231,269 Points

Higher? 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 a HAVING clause after the GROUP BY.

Bruce McMinn
Bruce McMinn
10,030 Points
Bruce McMinn
Bruce McMinn
10,030 Points

Right 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!

SELECT title, COUNT(*) as book_num
FROM (SELECT title FROM books_north 
      UNION ALL
      SELECT title FROM books_south)    
GROUP BY title
HAVING book_num>1  
ORDER BY book_num;
Steven Parker
Steven Parker
231,269 Points
Steven Parker
Steven Parker
231,269 Points

Sure, 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.

Steven Parker
Steven Parker
231,269 Points
Steven Parker
Steven Parker
231,269 Points

Allan 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
Dan Coleman
2,292 Points
Dan Coleman
Dan Coleman
2,292 Points

You 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
Simon Coates
28,694 Points
Simon Coates
Simon Coates
28,694 Points

this 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
Stamos Bolovinos
4,320 Points
Stamos Bolovinos
Stamos Bolovinos
4,320 Points

I 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
Steven Parker
231,269 Points
Steven Parker
Steven Parker
231,269 Points

:warning: This 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
Stamos Bolovinos
4,320 Points
Stamos Bolovinos
Stamos Bolovinos
4,320 Points

Hi 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
Steven Parker
231,269 Points
Steven Parker
Steven Parker
231,269 Points

That'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
Stamos Bolovinos
4,320 Points
Stamos Bolovinos
Stamos Bolovinos
4,320 Points

I 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
Steven Parker
231,269 Points
Steven Parker
Steven Parker
231,269 Points

I'd say "good job" if this actually related to the original question. :smirk: