sql - MySQL function IFNULL not working with GROUP BY -

i've got standard table list of users , i've got column lastactivity unix timestamp (which shows when have logged in) , column timestamp unix timestamp shows when have registered.

i've build sql query shows how many users active within 24 hours (86400 seconds) , grouped results weeks counter counts how many users have registered each week:

select     ifnull(count(*),0) `counter`,     (week(`timestamp`)) `week`     `clients`     (cast(unix_timestamp() signed) - cast(`lastactivity` signed)) <= 86400 group     week(`timestamp`); 

the issue function ifnull(count(*),0) not working intended. sql query won't display week if there null / 0 on counter ifnull() mysql function. because of how group by works. example kind of result:

counter | week    2    |  11    1    |  13    9    |  14    6    |  17 

but show each week this:

counter | week    2    |  11    0    |  12    1    |  13    9    |  14    0    |  15    0    |  16    6    |  17 

anyone have idea how can fix issue?

gordon trying me getting left join query still got same results, maybe doing wrong here:

select     count(a.id) `counter`,     (week(b.timestamp)) `week`     `users` left join     `users` b on     a.id = b.id     (cast(unix_timestamp() signed) - cast(a.lastactivity signed)) <= 86400 group     week(b.timestamp); 

this long comment.

the problem don't understand how query works. ifnull() (or standard version coalesce() converts column value null other value. however, count() never returns null. so, leave out:

select count(*) `counter`, week(`timestamp`) `week` `clients` (cast(unix_timestamp() signed) - cast(`lastactivity` signed)) <= 86400 group week(`timestamp`); 

your problem missing rows, not null values. have solve left join.

edit:

you need left join include weeks:

select count(c.timestamp) `counter`, wk `week` (select 11 wk union       select 12 union       select 13 union       select 14 union       select 15 union       select 16 union       select 17      ) w left join      `clients` c      on week(c.`timestamp`) = w.wk (cast(unix_timestamp() signed) - cast(`lastactivity` signed)) <= 86400 group week(`timestamp`);