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

Josh Keenan
Josh Keenan
20,315 Points
Posted by Josh Keenan
Josh Keenan
20,315 Points

Failing on a test but getting the right output, help?

select genre, count(genre) as genre_count from books where genre not null group by genre;

That's my code and this is the test:

https://teamtreehouse.com/library/reporting-with-sql/aggregate-and-numeric-functions/counting-groups

Any help or guidance would be appreciated!

Josh Keenan
Josh Keenan
20,315 Points
Josh Keenan
Josh Keenan
20,315 Points

Ahhhh, okay thanks a lot Steven, I really appreciate it!

1 Answer

Steven Parker
Steven Parker
231,269 Points
Steven Parker
Steven Parker
231,269 Points

You're really close, but I don't think they intended for you to exclude items without a genre. Those should show up as a separate count with an empty genre name. So besides not needing a filter, you'll also need to count the rows and not specifically genres.

Josh Keenan
Josh Keenan
20,315 Points
Josh Keenan
Josh Keenan
20,315 Points

Sadly without the not null it fails, I have just given up on it now as I don't understand it. These are all the solutions I have tried so I really am done. 

select genre, count(genre) as genre_count from books where genre not null group by genre;
select genre, count(genre) as genre_count from books;
select genre, count(genre) as genre_count from books group by genre;
select genre, count(genre) as genre_count from books group by genre where genre not null;
select genre, count(genre) as genre_count from books group by genre_count;
select genre, count(genre) as genre_count from books where genre not null group by genre_count;
Steven Parker
Steven Parker
231,269 Points
Steven Parker
Steven Parker
231,269 Points

You nearly had it with that third one. When I suggested "count the rows and not specifically genres", that means instead of "count(genre)" you might use "count(1)" or "count(*)".

Josh Keenan
Josh Keenan
20,315 Points
Josh Keenan
Josh Keenan
20,315 Points

How does that work, I now know you're number one on databases so I am hoping you can help, why does count(genre) return 0 for the book without a genre and count(1) work it out?

Steven Parker
Steven Parker
231,269 Points
Steven Parker
Steven Parker
231,269 Points

When you count a particular column, it counts only the number of non-null values that appear in that column. But when you "count(1)" it counts the number of rows instead.