Selecting a COUNT(*) vs. COUNT({field name}) in a JOIN

In an exercise for INNER JOIN, I’m joining the newspaper table with the article table on the id field, which is common to both tables. I want to get a COUNT of rows from the resulting joined table, and I initially did COUNT(id), since id is in the joined table, but didn’t get a result. I also tried COUNT(email) as there is an email field, but I also didn’t get a result.

It only works with COUNT(), which is fine, and I’m sure that someone will say that there’s no need to include an expression in the COUNT() function. For the sake of this exercise, I know that’s true, although there’s a difference - COUNT() returns all rows, including NULL values, but COUNT() [with a field name] in the function doesn’t count NULL values.

Anyway, as I said, it’s not important here, but does anyone know why a COUNT() - with a field that is definitely in the joined table - wouldn’t work?

https://www.codecademy.com/courses/learn-sql/lessons/multiple-tables/exercises/inner-join-ii

Problem is there are two id columns so you need to disambiguate.

select count(n.id) from newspaper n join online o on n.id = o.id

or

select count(o.id) from newspaper n join online o on n.id = o.id

depending on which id’s we want to count.

You would hope a dbms app would give you a nice error message for that.

1 Like

Thank you! I agree, I would’ve hoped for something to indicate the nature of the error, but the only indication of an error is that no results are returned.