how to count NULL categories in one SQL question

Question

hi,

i have a blog application were Post belongsTo Category and Category hasMany Post

Post can have a Category or not - in latter case NULL value is present in Post.category_id field.

Now i would like to have following category count with single SQL query

category|post_count
--------------
PHP | 2
JavaScript | 4
SomeOtherCat | 1
NULL | 3

The clue here is that i also want to count posts without category (NULL row above). Is it posibble with one SQL query?

Answer 1

SELECT
  c.CategoryName,
  COUNT(*)
FROM
  Posts p
    left join
  Category c
    on
      p.Category_id = c.Category_id
group by
  c.CategoryName

(For the purposes of group by, all NULL results go into the same group. This can be surprising to some who are only used to the fact that NULL != NULL when writing conditions)

Answer 2

It should work with something like this:

SELECT C.category, COUNT(*) AS post_count
FROM Post P
LEFT JOIN Category C ON P.category_id=C.category_id
GROUP BY C.category

I'm not sure about the identity column in Category, you might need to replace with the correct name.

Edit: Oops - forgot the group by.

Answer 3

SELECT C.CategoryName, Count(P.Post_ID)
FROM Category C INNER JOIN Posts P
ON P.CategoryID = C.CategoryID
GROUP BY C.CategoryName
UNION
SELECT NULL, Count(P.Post_ID)
FROM Posts P
WHERE P.CategoryID IS NULL
GROUP BY NULL