I’m in lesson 98 “Grouping and counting with LEFT JOIN”, but I noticed something, when doing the following query:
SELECT name, COUNT(user_id) FROM books b LEFT JOIN books_users u ON u.book_id = b.id GROUP BY 1 ORDER BY 2 ASC
You actually get some books with 0 values, because nobody has started reading them. But then for avoiding duplicated values from same users we add the
DISTINCT, however, all books now again have at least a value of 1, I’m not sure why this happens.
SELECT name, COUNT(DISTINCT(user_id)) FROM books b LEFT JOIN books_users u ON u.book_id = b.id GROUP BY 1 ORDER BY 2 DESC