generate series equivalent in MySQL

Asked Oct 08 '13 at 16:18

Active Oct 08 '13 at 16:18

Viewed 90 times

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?

asked Oct 08 '13 at 16:18

mikiamomik

In MySQL you will need to create and populate a table containing all possible dates in order to do this. – a_horse_with_no_name 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

generate series equivalent in MySQL

0 Answers0