1

I have a query like this

SELECT DATE(date) AS DateOnly, COUNT(DISTINCT(device_id)) AS nrUnique 
FROM `mytable`
WHERE date>='2013-10-04 18:32:04' and date<now()
GROUP BY DateOnly 
order by DateOnly

result

2013-10-05 | 10
2013-10-08 | 5

I want to generate a result like this

2013-10-04 | 0
2013-10-05 | 10
2013-10-06 | 0
2013-10-07 | 0
2013-10-08 | 5

I've do this in postgresql using a union like this 

....
UNION
(SELECT 0 as count, date(generate_series('2013-10-04 18:32:04'::date, now(),'1 day')) as timestamp))

How I can do this with MYSQL?

mikiamomik
  • 43
  • 2
  • 9
  • In MySQL you will need to create and populate a table containing all possible dates in order to do this. –  Oct 08 '13 at 16:27
  • @a_horse_with_no_name: I'm pretty sure you can fake it in MySQL using variables, see the two duplicates at the top of the **Related** list. – mu is too short Oct 08 '13 at 17:00
  • 1
    @muistooshort you still need a donor table to do that. And although any table would do this method ain't too reliable. – Jakub Kania Oct 08 '13 at 17:16

0 Answers0