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!
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
Riley Egan2,452 Points
I'm at a loss as far as what to do. I've re-watched the video several times and cannot get past this point.
I've tried this question so many different ways. It tells me I need a SELECT DISTINCT and a COUNT() function but I can't see anything in the video that had combined those two.
Alex KoumparosPython Development Techdegree Student 36,886 Points
This is definitely a tough challenge, neither of the videos so far handle exactly the case we are looking at. Moreover, because the challenge doesn't show us the output of our query, it's difficult to zero in on exactly what we want (and we don't actually need DISTINCT in part 1).
However, we can use the various discussions in the previous videos, together with the workspace to figure out the result.
If you load the workspace from the second video, we get the app that Andrew has been using in the video. We can use the
products table as a reasonable proxy for the
books table and try out some queries until we get something that looks like what the question is asking for.
products table has a
category column that is approximately equivalent to
genre in our
books table. Therefore, if we can construct a query that looks like this:
Then that query will probably (with the appropriate name substitutions) work for the challenge.
The challenge tells us which columns (the genre and the genre_count) we want in our output, so we can start building our selection with that:
SELECT category, <something> AS category_count FROM products;
For now, let's just use a simple COUNT statement in place of
<something> so we can get a valid output:
SELECT category, COUNT(category) AS category_count FROM products;
Not exactly what we want, but it's got the right columns at least.
Right now, the
category_count column is giving us the total number of rows in the table that have a value for
category. but we know that we want our output to be the number of items*for each genre* in our table. This means
we need to use GROUP BY (from the second video).
Let's just append that to our statement and see how it changes:
SELECT category, COUNT(category) as category_count FROM products GROUP BY category;
As it happens, this looks exactly like what we want, but there is an issue (I think this was dealt with in the first video): suppose that there some records with a null value for
category, these won't be counted. So instead, we want to count on a field that cannot be null (e.g., id), or count the whole row (*):
SELECT category, COUNT(*) as category_count FROM products GROUP BY category;
In this case, we get the same answer, but if you make the corresponding changes for the books, you'll see an output that would have tripped up the previous query:
Hope that helps you out.
KRIS NIKOLAISEN54,940 Points
The syntax for count distinct in SQLite is:
SELECT COUNT(DISTINCT column1) AS alias_name FROM table_name;