MySQL, count values by key

Question

I need to count values on a table accordingly with their presence or missing.

With a clear example consider a table with 2 field:

+-------+---------+
| K     | V       |
+-------+---------+
| key1  | 10      |
| key1  | 20      |
| key1  | Stefano |
| key1  | 40      |
| key1  | 50      |
| key1  | 60      |
| key1  | Stefano |
| key1  | 80      |
| key1  | 90      |
| key1  | 100     |
| key1  | 110     |
+-------+---------+
| key2  | 11      |
| key2  | 21      |
| key2  | 31      |
| key2  | Stefano |
| key2  | 51      |
| key2  | 61      |
| key2  | 71      |
| key2  | 81      |
| key2  | 91      |
| key2  | 101     |
+-------+---------+
| key3  | 13      |
| key3  | 23      |
| key3  | 33      |
+-------+---------+

My goal is count occurrences of V = 'Stefano' getting -1 if K is not existing:

select count(*) from TableName where K = 'key1' and V = 'Stefano'
> 2

select count(*) from TableName where K = 'key2' and V = 'Stefano'
> 1

select count(*) from TableName where K = 'key3' and V = 'Stefano'
> 0

How I can get value [ -1 ] querying on a missing key value?

select count(*) from TableName where K = 'missing_key' and V = 'Stefano'
> -1

Answer 1

Seems silly that you would want -1 rather than 0, but one method is:

select coalesce(nullif(count(*), 0), -1)
from TableName
where K = 'missing_key' and V = 'Stefano';

If you wanted a special non-0 value, I would suggest NULL.

Note: You can do this all in a single query:

select k, sum( v = 'Stefano' )
from tablename
group by k;

You can use the coalesce(nullif()) logic here too.

EDIT:

If you only want -1 when the key is missing, then:

select (case when count(*) = 0 then -1
             else sum(v = 'Stefano')
        end)
from TableName
where K = 'missing_key';