Mysql IFNULL working only once in a multiple SELECT query

Question

I'm trying to build a month graphic using a MySQL query. I'm checking how many rows there are in a table for each month in a single query using the UNION command. Example with 3 months bellow:

$query =
"SELECT IFNULL((SELECT SUM(score) FROM statistics WHERE MONTH(date) = 1), 0) AS total UNION
 SELECT IFNULL((SELECT SUM(score) FROM statistics WHERE MONTH(date) = 2), 0) AS total UNION
 SELECT IFNULL((SELECT SUM(score) FROM statistics WHERE MONTH(date) = 3), 0) AS total";

$stats_query = mysqli_query ($db_connection, $query);

  $result = "";
  while ($row = mysqli_fetch_assoc($stats_query)) {
    $result .= $row['total'].",";
  }
  echo ($result);

// OUTPUT: 0,176,68,

As you can see, I'm telling mysql to return me a "0" in case there are no rows for that month (which is the case for January).

There are a total of 12 SELECTS in that query (I copied just 3 to save space), one for each month. Some months will return a value, others won't (which the IFNULL should then convert to a "0"). My final output, for all the 12 months, should look like this:

// OUTPUT: 0,176,68,0,0,0,0,0,12,15,176,43,

BUT... if there is more than one SELECT that returns no rows, the query won't add another "0" to the result. My final result ends up being like this:

// OUTPUT: 0,176,68,12,15,176,43,

It's like the IFNULL is only executed once, even though he's present in all the 12 SELECTS...

Am I doing something wrong? Can anyone spot an error in my code or something?

Thank you!

`UNION ALL`, `UNION` gives `DISTINCT` result – Lukasz Szozda Sep 14 '15 at 17:51 — Lukasz Szozda, Sep 14 '15 at 17:51

Lukasz Szozda · Accepted Answer · 2015-09-14T17:59:02.770

3

Use UNION ALL instead of UNION to get all results:

 SELECT IFNULL((SELECT SUM(score) FROM statistics WHERE MONTH(date) = 1), 0) AS total UNION ALL
 SELECT IFNULL((SELECT SUM(score) FROM statistics WHERE MONTH(date) = 2), 0) AS total UNION ALL
 SELECT IFNULL((SELECT SUM(score) FROM statistics WHERE MONTH(date) = 3), 0) AS total

UNION returns only DISTINCT rows.

From doc:

The default behavior for UNION is that duplicate rows are removed from the result.

edited Sep 14 '15 at 17:59

answered Sep 14 '15 at 17:52

Lukasz Szozda

162,964
23
234
275

1

Oh, really simple... I didn't know UNION would return me only distinct rows. UNION ALL solved my problem instantly. Thank you. – DanielFox Sep 14 '15 at 17:55

Mysql IFNULL working only once in a multiple SELECT query

1 Answers1