include empty rows in mysql result

Question

I have a simple table of timestamps , types and counts like

timestamp |t|c
==============
1415024797|1|1
1415025774|1|1
1415202785|1|1
1415204559|1|1
1415204593|1|2
1415629057|1|1
1415791322|2|1
1415797887|1|1

now i get a result which counts the c column group by t and for a certain date YYYY-MM-DD

I have to add 3600 to the timestamp to respect timezone offset!

SELECT From_unixtime(a.timestamp + 3600, '%Y-%m-%d')        AS date, 
       Count(From_unixtime(a.timestamp + 3600, '%Y-%m-%d')) AS count, 
       a.t  AS type
FROM   table AS a
WHERE a.timestamp >= 1415322000 AND a.timestamp < 1415926800
GROUP  BY From_unixtime(a.timestamp + 3600, '%Y-%m-%d'), 
          a.t
ORDER  BY a.timestamp DESC

with this query I get something like

date       |count | type
==========================
2014-12-03 | 3    | 1
2014-12-03 | 1    | 2
2014-12-04 | 3    | 1
2014-12-05 | 3    | 3
2014-12-07 | 4    | 2
2014-12-07 | 7    | 3
....

But I would like to get

date       | t_1 | t_2 | t_3
=============================
2014-12-03 | 3   | 1   | 0
2014-12-04 | 3   | 0   | 0    
2014-12-05 | 0   | 0   | 3 
2014-12-06 | 0   | 0   | 0   
2014-12-07 | 0   | 4   | 7   
....

So on each line a date with all counts of a certain type.

There are only 3 types possible (1 => t_1, 2 => t_2, 3 => t_3)
Also dates with 0 values (2014-12-06) should be included
to build the query I'll use PHP (foreach)

Gonzalo1987 · Accepted Answer · 2014-11-14 16:39:59Z

1

SELECT From_unixtime(a.timestamp + 3600, '%Y-%m-%d')        AS date,
    CASE 
       WHEN type = 1 
          THEN count 
       ELSE 
          NULL 
    END AS t_1,
    CASE 
       WHEN type = 2 
       THEN count ELSE NULL END AS t_2,
    CASE 
       WHEN type = 3 
       THEN count ELSE NULL END AS t_3
FROM...

(?)

edited Nov 14, 2014 at 16:39

answered Nov 14, 2014 at 16:27

Gonzalo1987

8713 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Xaver Over a year ago

Yes, but I need empty values as well (2014-12-06)

Gonzalo1987 Over a year ago

in the part of 'else null' you could put "else '' " or "else 0"

paul · Accepted Answer · 2014-11-14 17:09:39Z

1

There is a relatively simple way of doing this, but the amount of repetitious/ugly code increases along with the number of values of t:

what you basically do is:

select date,
       count(case when type=1 then 1 else null end) as t1_count,
       count(case when type=2 then 1 else null end) as t2_count... 
et cetera

If you are expecting a lot of different values of t, this becomes quite impractical, and you'd have to look at more complex techniques such as this one: http://www.artfulsoftware.com/infotree/qrytip.php?id=523

edit: If you want to include dates which have no data at all, then you should consider creating a date table, and then 'left joining' it to your results set. you could use this code (https://gist.github.com/johngrimes/408559) - just ignore the first two statements that create numbers tables.

edited Nov 14, 2014 at 17:09

answered Nov 14, 2014 at 16:26

paul

22.1k1 gold badge56 silver badges54 bronze badges

2 Comments

Xaver Over a year ago

Yes, but I need empty values as well (2014-12-06)

Xaver Over a year ago

Can you share an example?

Collectives™ on Stack Overflow

include empty rows in mysql result

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related