ifnull not working on mysql

Question

I have the following table:

+----+------------+----------+------------------+
| id | created_at | platform | platform_version |
+----+------------+----------+------------------+
|  1 |          1 | mac      | 1                |
|  2 |          2 | mac      | 1                |
|  3 |          3 | mac      | 2                |
|  4 |          4 | mac      | 2                |
|  5 |          5 | mac      | 2                |
|  6 |          5 | mac      | 3                |
|  7 |          2 | windows  | 1                |
|  8 |          2 | windows  | 2                |
|  9 |          3 | windows  | 3                |
| 10 |          3 | windows  | 1                |
| 11 |          4 | windows  | 2                |
| 12 |          4 | windows  | 3                |
| 13 |          5 | windows  | 4                |
| 14 |          5 | windows  | 1                |
| 15 |          6 | windows  | 2                |
| 16 |          6 | windows  | 3                |
+----+------------+----------+------------------+

I want to have a result like the following:

+-------------+---------------+---------------+------------+
| group_count | running_total | windows_total | created_at |
+-------------+---------------+---------------+------------+
|           1 |             1 |             0 |          1 |
|           6 |             7 |             4 |          2 |
|           7 |            14 |             8 |          4 |
|           2 |            16 |            10 |          6 |
+-------------+---------------+---------------+------------+

But when I execute this select statement:

SELECT group_count,
       (@r := @r + group_count ) AS running_total,
       (@w := @w + ifnull( win_count, 0 )) AS windows_total,
       t1.created_at
FROM  (SELECT   (@r :=0),
                COUNT( * ) AS group_count,
                platform,
                created_at
       FROM     devices
       GROUP BY created_at DIV 2
      ) AS t1 LEFT JOIN
      (SELECT   COUNT( * ) AS win_count,
                created_at
       FROM     devices
       WHERE    platform = 'windows'
       GROUP BY created_at DIV 2
      ) AS t3 ON t1.created_at = t3.created_at

It comes out like this:

+-------------+---------------+---------------+------------+
| group_count | running_total | windows_total | created_at |
+-------------+---------------+---------------+------------+
|           1 |             1 |          NULL |          1 |
|           6 |             7 |          NULL |          2 |
|           7 |            14 |          NULL |          4 |
|           2 |            16 |          NULL |          6 |
+-------------+---------------+---------------+------------+

coalesce also doesn't work. Could anyone help? Thanks.

`@w` is initially `NULL`, so adding it to the result of `IFNULL` (whatever that result is) produces `NULL`. Give it an initial value in the subselect, as you have done with `@r`. — eggyal, Jun 07 '12 at 13:38

score 0 · Answer 1 · answered Jul 22 '12 at 23:09

For the record, I quote @eggyal's answer:

@w is initially NULL, so adding it to the result of IFNULL (whatever that result is) produces NULL.
Give it an initial value in the subselect, as you have done with @r

SELECT group_count,
       (@r := @r + group_count ) AS running_total,
       (@w := @w + ifnull( win_count, 0 )) AS windows_total,
       t1.created_at
FROM  (SELECT   (@r:= 0),(@w:= 0),
                COUNT(*) AS group_count,
                platform,
                created_at
       FROM     devices
       GROUP BY created_at DIV 2
      ) AS t1 LEFT JOIN
      (SELECT   COUNT( * ) AS win_count,
                created_at
       FROM     devices
       WHERE    platform = 'windows'
       GROUP BY created_at DIV 2
      ) AS t3 ON t1.created_at = t3.created_at

This should solve the issue.

ifnull not working on mysql

1 Answers1