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
Laurence kite
11,768 PointsSQL queries
I am attempting do answer some the practice questions in the sql playground set by Chalkers but having problems get the exact output
Here is the practice question :
-- Generate a report that lists the book titles from both locations and count the total number of books with the same title.
Here is my attempt
SELECT books_north.title FROM books_north
UNION ALL
SELECT books_south.title FROM books_south
UNION ALL
SELECT COUNT(books_north.title) AS "Duplicate Books Count" FROM books_north
INNER JOIN books_south ON books_north.title = books_south.title;
```
This does not list the Duplicate Book Count as a heading but just the total this being 12?
This is my output :
title
A Brief History of Time
Armada
Emma
Frankenstein
Harry Potter and the Chamber of Secrets
Harry Potter and the Deathly Hallows
Harry Potter and the Goblet of Fire
Harry Potter and the Half-Blood Prince
Harry Potter and the Order of the Phoenix
Harry Potter and the Philosopher's Stone
Harry Potter and the Prisoner of Azkaban
Pride and Prejudice
Ready Player One
The Martian
The Universe in a Nutshell
A Brief History of Time
Congo
Emma
Frankenstein
Harry Potter and the Chamber of Secrets
Harry Potter and the Deathly Hallows
Harry Potter and the Goblet of Fire
Harry Potter and the Half-Blood Prince
Harry Potter and the Order of the Phoenix
Harry Potter and the Philosopher's Stone
Harry Potter and the Prisoner of Azkaban
Jurassic Park
Pride and Prejudice
Sphere
The Universe in a Nutshell
12
I want the last bit to have the heading Duplicate Book Count but not sure how to achieve this?
I have tried using NULLs but this doesn't really help
5 Answers
Steven Parker
231,269 PointsWhen you perform a UNION, the first query establishes what the quantity, names, and types of the output columns will be. All others being UNIONed must match in number and types of columns, and any other names are ignored.
I can probably give you more specific help once I see what you're working on.
Update: Now that I've seen the challenge, I think that "total" they are asking for is per title. So your output should look something like this (I named the "total" column "Copies"):
Title Copies
----- ------
A Brief History of Time 2
Armada 1
Congo 1
Emma 2
Frankenstein 2
Harry Potter and the Chamber of Secrets 2
Harry Potter and the Deathly Hallows 2
Harry Potter and the Goblet of Fire 2
Harry Potter and the Half-Blood Prince 2
Harry Potter and the Order of the Phoenix 2
Harry Potter and the Philosopher's Stone 2
Harry Potter and the Prisoner of Azkaban 2
Jurassic Park 1
Pride and Prejudice 2
Ready Player One 1
Sphere 1
The Martian 1
The Universe in a Nutshell 2
You can do this with JOINs or UNIONs, and you will probably also want to use GROUP BY.
Laurence kite
11,768 PointsSELECT title, COUNT(title) AS Copies FROM
(SELECT title FROM books_south UNION ALL SELECT title FROM books_north) AS allb
GROUP BY title;
A Brief History of Time 2 Armada 1 Congo 1 Emma 2 Frankenstein 2 Harry Potter and the Chamber of Secrets 2 Harry Potter and the Deathly Hallows 2 Harry Potter and the Goblet of Fire 2 Harry Potter and the Half-Blood Prince 2 Harry Potter and the Order of the Phoenix 2 Harry Potter and the Philosopher's Stone 2 Harry Potter and the Prisoner of Azkaban 2 Jurassic Park 1 Pride and Prejudice 2 Ready Player One 1 Sphere 1 The Martian 1 The Universe in a Nutshell 2
I would like to know how to use a join to get the results
Steven Parker
231,269 PointsSorry, I was thinking of FULL OUTER JOIN, which is not supported by the playground.
But, you got it!
Laurence kite
11,768 PointsHI there thanks for getting back to me
Here is the link to the course :
https://teamtreehouse.com/library/using-a-subquery-to-create-a-temporary-table-part-1
Here is the bit I am struggling with its a playground of testing sql
https://teamtreehouse.com/sql_playgrounds/522#/queries/670ffc6e-68f8-4117-aeda-a3bd5d37d4b3
This is my revised code
SELECT books_north.title AS "Book Titles Both", COUNT(books_north.title) AS "Duplicate Books Count" FROM books_north
INNER JOIN books_south ON books_north.title = books_south.title
UNION ALL
SELECT books_north.title, NULL FROM books_north
UNION ALL
SELECT books_south.title, NULL FROM books_south;
I really wanted the Duplicate Books Count displayed at the bottom, I am not sure if what I have done produces the requested results
Which are :
-- Generate a report that lists the book titles from both locations and count the total number of books with the same title.
Laurence kite
11,768 PointsI am not sure how to do it with JOINS as I am new to this and JOINS seem to find matching rows and not books that are just in one table..
Any hints lol
Steven Parker
231,269 PointsDisregard the comments about JOIN, you can use UNION ALL to create a derived table of just titles, and then an outer query using COUNT and GROUP BY to make the final list like the one shown in my answer.
Laurence kite
11,768 PointsA Brief History of Time 2
Armada 1
Congo 1
Emma 2
Frankenstein 2
Harry Potter and the Chamber of Secrets 2
Harry Potter and the Deathly Hallows 2
Harry Potter and the Goblet of Fire 2
Harry Potter and the Half-Blood Prince 2
Harry Potter and the Order of the Phoenix 2
Harry Potter and the Philosopher's Stone 2
Harry Potter and the Prisoner of Azkaban 2
Jurassic Park 1
Pride and Prejudice 2
Ready Player One 1
Sphere 1
The Martian 1
The Universe in a Nutshell 2
Steven Parker
231,269 PointsSteven Parker
231,269 PointsCan you provide a link to the course page you are working with?