Return zero on count function - Mysql

Question

Here is the problem,

I've got a very large table like this :

Epoch_time              MAC
-------------------------------------
1395275303  |   84:xx:xx:xx:xx:xx 
1395275423  |   bc:xx:xx:xx:xx:xx 
1395275423  |   84:xx:xx:xx:xx:xx

And I want to count the unique MAC Adress per Hour. So I did this

    SELECT Count(DISTINCT mac_adress), FROM_UNIXTIME(`date_ecoute`, '%d.%m.%Y.%H') as ndate FROM box1 
WHERE date_ecoute > unix_timestamp(CURRENT_TIMESTAMP - INTERVAL 2640 MINUTE) 
AND signal_strength >= -60 group by ndate

That works pretty well, here is the result per hour :

Count(DISTINCT mac_adress)  ndate
--------------------------------------
2              |          20.03.2014.03
8              |          20.03.2014.06
11             |          20.03.2014.07

The problem is that some hours there are no line in my table so as you can see on the result above there are no line for 20.03.2014.04 or even 05. I would like this output :

  Count(DISTINCT mac_adress)    ndate
    --------------------------------------
    2              |          20.03.2014.03
    0              |          20.03.2014.04
    0              |          20.03.2014.05
    8              |          20.03.2014.06
    11             |          20.03.2014.07

I tried IS NULL, IF NULL, LEFT JOIN, COALESCE. I'm not pro with Sql...

IFNULL(Count(DISTINCT mac_adress),0) // At the beginning, Doesn't work

See this post, http://stackoverflow.com/questions/21063779/returning-empty-rows-in-group-by-clause — Beauvais, Mar 21 '14 at 22:27

Gordon Linoff · Answer 1 · 2014-03-21T22:49:01.983

If you assume that you have some data for all hours in the data, then you can get the 0s by using conditional aggregation. That is, move conditions from the where clause to a case statement in the select clause:

SELECT Count(DISTINCT case when signal_strength >= -60 then mac_adress end),
      FROM_UNIXTIME(`date_ecoute`, '%d.%m.%Y.%H') as ndate
FROM box1 
WHERE date_ecoute > unix_timestamp(CURRENT_TIMESTAMP - INTERVAL 2640 MINUTE) 
group by ndate
order by ndate;

If you don't have data for all time stamps, then you will need to use a "driver" table (or something similar) that contains all the hours you want in the output.

EDIT:

If you have data on every day, you can do this:

SELECT d.ndate, h.hour,
       Count(DISTINCT case when then mac_adress end)
from (select distinct FROM_UNIXTIME(`date_ecoute`, '%d.%m.%Y') as ndate
      from box1
      where date_ecoute > unix_timestamp(CURRENT_TIMESTAMP - INTERVAL 2640 MINUTE) 
     ) d cross join
     (select 0 as hour union all select 1 union all select 2 union all select 3 union all
      . . .
     ) h left outer join
     box1
where date_ecoute > unix_timestamp(CURRENT_TIMESTAMP - INTERVAL 2640 MINUTE)  and
      signal_strength >= -60 
group by ndate, hour
order by min(dte_ecoute), hour;

Let me add . . . if you have the choice, the "YYYY-MM-DD" format is the best to use for string representations. In particular, you can use it for ordering directly.

Ok I'll have to create a "driver table then, i wanted to avoid this but i think this is the only way. Many thanks ! — user3385712, Mar 21 '14 at 22:37

Return zero on count function - Mysql

1 Answers1