Mysql IFNULL只在多个SELECT查询中工作一次

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!

Answer 1

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.