What's Wrong With This Code (#6)

DISTINCT at the SELECT level will only ensure that unique rows appear in the result set. In this example, DISTINCT should be used within each COUNT() to ensure that duplicates are eliminated before the counts are calculated:

SELECT
COUNT(DISTINCT Authors.Id) AS TotalAuthors,
COUNT(DISTINCT Books.Id) As TotalBooks
FROM
Authors
FULL JOIN AuthorsBooks AB ON Authors.Id = AB.AuthorID
FULL JOIN Books ON AB.BookID = Books.Id

DISTINCT should be place within the count function, this way you are only counting the distinct values within each column.

SELECT
COUNT(DISTINCT Authors.Id) AS TotalAuthors,
COUNT(DISTINCT Books.Id) As TotalBooks
FROM
Authors
FULL JOIN AuthorsBooks AB ON Authors.Id = AB.AuthorID
FULL JOIN Books ON AB.BookID = Books.Id

Julian, Eric: Correct!

I also updated the post to fix a small typo (not in the code).

Um, I might be missing something, but wouldn't it be much (much!!) faster and simpler to just SELECT COUNT(*) from the Authors and Books tables in order to get the author and book counts?

It could be, Wilhelm [but then it wouldn't be interesting to look at for bugs :)]

In fact, I think AuthorsBooks is just confusing the developer. If you want 1 resultset containing a count of authors and a count of books the query would be better written like this:

-- use count(1) not count(*) to speed up
select
(select count(1) from authors) as [AuthorsCount]
, (select count(1) from books) as [BooksCount]

I am a bit ignorant about this. What tool have you used for the ER diagram?

@ Rick: Good one, I like that.

@ yaip: This is the database diagram tool in SQL Server 2005. It is similar to the same tool in SQL 2000, just a slightly different look.

@rick - count(*) and count(1) make no difference. unless that's an access thing of course but turn all the profiling options on in sqlserver and you'll see no difference.

just trying to squash that myth