Review and Practice - Subqueries

I would assume that the task was asking for what all the above answers are getting as a result. But, if you reread it carefully, it's asking for a report listing all book titles from both locations AND the total number of books with the same title. So, it can also mean list all the titles as well as the number of books with the same title, all in the same query.

In any case, I attempted to do it and managed to make it work, though it doesn't look really readable. Both the query and to some extent the result :). Also, note there are two ways of adding this information for the number of books with the same title to the result.

It can be done as an additional row in the result set.

This is the query:

SELECT title, COUNT(*) AS Number FROM (
  SELECT title FROM books_north
  UNION ALL
  SELECT title FROM books_south
  ) GROUP BY title
UNION
SELECT "Total number of books with the same title", COUNT(*)
FROM (SELECT title, COUNT(*) AS numBooks FROM (
  SELECT title FROM books_north
  UNION ALL
  SELECT title FROM books_south
  ) GROUP BY title) AS t
WHERE t.numBooks >= 2

This will display the list of book titles and the bottom row will the display the number of books with the same title. It works by just adding a single row to the resultset using UNION and the first column is hardcoded to show "Total number of books with the same title" and the second column displays the number of the books with the same title, which for this example is 12.

Now, there's also another option and that is to add an additional column to the result set to present the number of books with the same title, and this is the query:

SELECT * FROM 
    (SELECT title, COUNT(*) AS Number FROM (
      SELECT title FROM books_north
      UNION ALL
      SELECT title FROM books_south
      ) GROUP BY title
    )
LEFT OUTER JOIN
    (SELECT * FROM
    (SELECT COUNT(*) AS "Total number of books with the same title"
    FROM (SELECT title, COUNT(*) AS numBooks FROM (
      SELECT title FROM books_north
      UNION ALL
      SELECT title FROM books_south
      ) GROUP BY title) AS t
    WHERE t.numBooks >= 2))

So we have a third column here labeled "Total number of books with the same title" added using a LEFT OUTER JOIN (cause other's weren't supported by SQLite, right?) without a join condition, so it's just added, no questions asked as far as I can tell. The downside of this approach is that it shows the number in every row. But it could be considered more readable since we don't have to scroll down to the bottom to get the required information about the number of books with the same title.

There probably is a way to avoid showing 1 and 2 for each of the book titles and make it all cleaner, like removing the second column altogether in the second approach by using an additional query on top of the one above which selects only the first and third column, but I'll stop here :).