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

Posted by Gleb Sklyr
Gleb Sklyr
17,492 Points

Why is the a NULL genre value complicating this challenge and howto solve it elegantly?

So this was my query to find the count of all unique genres in books:

SELECT COUNT(*) AS 'total_genres' FROM (SELECT DISTINCT genre FROM books) G;

Now this returned 7 which i figured eventually was one more than the actual number because of NULL values in some rows for genre... So this worked:

SELECT COUNT(*)-1 AS 'total_genres' FROM (SELECT DISTINCT genre FROM books) G;

But this seems like a recreational solution... Is there a nicer more robust way to solve this?

egaratkaoroprat
egaratkaoroprat
16,630 Points

I am not sure about this one, but have you already tried SELECT COUNT(*) AS 'total_genres' FROM (SELECT DISTINCT genre FROM books WHERE genre IS NOT NULL)?

Gleb Sklyr
Gleb Sklyr
17,492 Points

Thanks Egarat! This seems to be one better way to solve this! I am not sure how I can comment under your answer or mark it as best answer but maybe theydo not have this option anymore...

1 Answer

egaratkaoroprat
egaratkaoroprat
16,630 Points

Hey Gleb,

I was too lazy to scroll down and put the answer in the corresponding field and just used the comment field instead (that's why you could not mark it as the correct solution)... :-) I am glad to be of help, if you don't know the reason why this works - just let me know.

But here is the solution again:

SELECT COUNT(*) AS 'total_genres' FROM (SELECT DISTINCT genre FROM books WHERE genre IS NOT NULL)

Happy coding!