showing Zero if sql count is NULL

Question

I have the following query (executed through PHP). How can I make it showing ZEROs if the result is NULL and is not shown.

select count(schicht) as front_lcfruh,
       kw,
       datum
from dienstplan
  left join codes on dienstplan.schicht = codes.lcfruh
  left join personal on personal.perso_id = dienstplan.perso_id
where codes.lcfruh != ''
and   personal.status = 'rezeption'
and   dienstplan.kw = '$kw'
group by dienstplan.datum

Answer 1

I'm not entirely sure I understand the question, but I think you want this:

select count(codes.lcfruh) as front_lcfruh,
       dienstplan.kw,
       dienstplan.datum
from dienstplan
  left join codes on dienstplan.schicht = codes.lcfruh and codes.lcfruh <> ''
  left join personal on personal.perso_id = dienstplan.perso_id
and   personal.status = 'rezeption'
and   dienstplan.kw = $kw
group by dienstplan.datum, dienstplan.kw

If schicht comes from dienstplan there will always be a row for that (as that is the driving table). If I understand you correctly you want a 0 if no matching rows are found. Therefor you need to count the joined table.

Edit:
The condition where codes.lcfruh != '' turns the outer join back into an inner join because any "outer" row will have lcfruh as NULL and any comparison with NULL yields "unknown" and therefor the rows are removed from the final result. If you want to exclude rows in the codes table where the lcfruh has an empty string, you need to move that condition into the JOIN clause (see above).

And two more things: get used to prefixing your columns in a query with more than one table. That avoids ambiguity and makes the query more stable against changes. You should also understand the difference between number literals and string literals 1 is a number '1' is a string. It's a bad habit to use string literals where numbers are expected. MySQL is pretty forgiving as it always try to "somehow" work but if you ever user other DBMS you might get errors you don't understand.

Additionally your usage of group by is wrong and will lead to "random" values being returned. Please see these blog posts to understand why:

• http://rpbouman.blogspot.de/2007/05/debunking-group-by-myths.html
• http://www.mysqlperformanceblog.com/2006/09/06/wrong-group-by-makes-your-queries-fragile/

Every other DBMS will reject your query the way it is written now (and MySQL will as well in case you turn on a more ANSI compliant mode)

Answer 2

If you have no matching rows, then MySQL will return an empty set (here I have defined the fields at random, just to run the query):

mysql> CREATE TABLE dienstplan (kw varchar(10), datum integer, schicht integer, perso_id integer);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE codes (lcfruh varchar(2));
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE personal (perso_id integer, status varchar(5));
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> select count(schicht) as front_lcfruh,
    ->        kw,
    ->        datum
    -> from dienstplan
    ->   left join codes on dienstplan.schicht = codes.lcfruh
    ->   left join personal on personal.perso_id = dienstplan.perso_id
    -> where codes.lcfruh != ''
    -> and   personal.status = 'rezeption'
    -> and   dienstplan.kw = '$kw'
    -> group by dienstplan.datum;

Empty set (0.00 sec)

You can check how many rows were returned by the query. In this case you will get zero.

So you could modify your code like this (pseudo code):

$exec = $db->execute($query);
if ($exec->error() !== false) {
    // Handle errors. Possibly quit or raise an exception.
}
if (0 == $exec->count())
{
    // No rows. We return a default tuple
    $tuple = array(
        'front_lcfruh' => 0,
        'kw'           => $kw,
        'datum'        => null
    );
    handleTuple($tuple);
} else {
    while($tuple = $exec->fetch()) {
        handleTuple($tuple);
    }
}

Where handleTuple() is the function that formats or otherwise manipulates the returned rows.

Answer 3

SELECT dp.datum 
        , MIN(kw) -- To avoid adding it to GROUP BY clause
        , count(schicht) AS front_lcfruh
FROM dienstplan dp
LEFT JOIN codes co ON dp.schicht = co.lcfruh
LEFT JOIN personal pe ON pe.perso_id = dp.perso_id
WHERE dp.kw = '$kw' 
AND COALESCE(co.lcfruh, 'X') <> '' -- Handle NULLs, too
AND COALESCE(pe.status , 'rezeption' ) = 'rezeption' -- Handle NULLs, too
GROUP BY dp.datum
;

Answer 4

COUNT() can NEVER return NULL.

Lorenzo said (see comments) that I should provide references to help the SO community. Based on that feedback, I've edited my answer below.

1. You should consult the documentation of the COUNT() function for MySQL here.
2. You should also consider reading this article at Stack Overflow entitled "can COUNT(*) ever return NULL".
3. Or this one entitled "When does COUNT(*) return NULL"
4. Or this one entitled "COUNT(*) returns NULL"
5. Or this one entitled "Return NULL if COUNT(*) is zero"
6. Or this article on Oracle's forums entitled "COUNT never returns NULL?"

Answer 5

select COALESCE(count(schicht),0) as front_lcfruh,kw,datum
from dienstplan
  left join codes on dienstplan.schicht = codes.lcfruh
  left join personal on personal.perso_id = dienstplan.perso_id
where codes.lcfruh != ''
and   personal.status = 'rezeption'
and   dienstplan.kw = '$kw'
group by dienstplan.datum

just replace

count(schicht)

with

COALESCE(count(schicht),0)