Listing both null and not null in mysql query
- by tomasz
Let's say I have the table
NAME | ID | REF
foo1 | 1 | NULL
foo2 | 2 | 1234
foo2 | 3 | 567
foo1 | 4 | NULL
foo3 | 5 | 89
I'd like to count all instances of NULL and NOT NULL in one query so that I can say
NAME | null | not null
foo1 | 0 | 2
foo2 | 2 | 0
foo3 | 0 | 1
I could run these two queries
select NAME,count(*) from TABLE where REF is not null
select NAME,count(*) from TABLE where REF is null
But I'm sure there must be a simple way to do it in one mysql query.
