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 Reporting with SQL Aggregate and Numeric Functions Counting Groups

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.

2 Answers

Alex Koumparos
Alex Koumparos
Python Development Techdegree Student 36,887 Points

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:

category category_count
Books 20
Clothing 6
Electronics 3

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;
category category_count
Books 29

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;
category category_count
Books 20
Clothing 6
Electronics 3

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:

genre genre_count
Classic 2
Fantasy 7
Fiction 1
Horror 1
Non Fiction 2
Science Fiction 6

Hope that helps you out.



The syntax for count distinct in SQLite is:

SELECT COUNT(DISTINCT column1) AS alias_name FROM table_name;