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.

Hi Riley,

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.

The 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.

Cheers,

Alex

The syntax for count distinct in SQLite is:

SELECT COUNT(DISTINCT column1) AS alias_name FROM table_name;