MYSQL：除了0和null之外的所有值的GROUP BY？

Question

I have a simple SQL Query:

  SELECT tid, 
         COUNT(*) AS bpn
    FROM mark_list 
   WHERE userid = $userid 
GROUP BY tid

Now the column tid is basically a category list associated with each entry. The categories are unique numeric values.

What I am trying to do is get an overall count of how many records there as per userid, but I only want to count an entire category one time (meaning if category 3 has 10000 records, it should only receive a count of 1).

The caveat is that sometimes the category is listed as null or sometimes a 0. If the item has either a 0 or a null, it has no category and I want them counted as their own separate entities and not lumped into a single large category.

Answer 1

Wheeee!

SELECT SUM(`tid` IS NULL) AS `total_null`,
       SUM(`tid` = 0) AS `total_zero`, 
       COUNT(DISTINCT `tid`) AS `other`
  FROM `mark_list`
 WHERE `user_id` = $userid

Edit: note that if total_zero is greater than 0, you will have to subtract one from the "other" result (because tid=0 will get counted in that column)