mysql if result does't exist return 0

Question

I have query

SELECT FROM_UNIXTIME(1800 * FLOOR(date/1800)) AS period_start, 
       COUNT(*) AS count 
FROM readed_messages
GROUP BY period_start
ORDER BY period_start ASC

How to add clause if/else, if record doesn't exist. I would like to get commands return 0 and commands return count. I didn't know to do this.

*"I would like to get commands return 0 and commands return count."* - This phrase makes the least sense to me — GolezTrol, May 26 '15 at 23:37
if doesn't exist record in this time, don't return this row. Return records if count >= 1 — prybus, May 26 '15 at 23:39
still confused. are you saying if there are no records for period_start, you want period_start to show 0, instead of not be included? — pala_, May 26 '15 at 23:41
What value would you want to have for `period_start` if there is no data? Do you have a list of periods in another table? — GolezTrol, May 26 '15 at 23:44
@GolezTrol has it right. If you want to 'generate' missing rows, you need to get the rows from somewhere else. I'm guessing you're aggregating over half hour long periods, which means you'd need some sort of calendar tables covering years, months and days, and then an interval table with your period increments. — pala_, May 26 '15 at 23:48
Or a number generator to generate a fake calendar table from. But that in itself is [hard to do](http://stackoverflow.com/questions/304461/generate-an-integer-sequence-in-mysql) for a novice. — GolezTrol, May 26 '15 at 23:52

score 0 · Answer 1 · answered May 27 '15 at 00:19

Generating missing data is an annoying problem. Usually it's best left to the application level.

Failing that, and if you have to do it in MySQL, the missing data has to come from somewhere. This can be either a dynamic sequence of numbers built from user variables and a union cross join - or if we know our domain values (like we do in this case) we can pre-build some tables to take care of it.

In this case you're operating on dates, so we need a few tables that will help us generate all the values you're interested in. I'd suggest these tables:

calendar_years(y integer)
calendar_months(m integer)
calendar_days(d integer)
calendar_hours(h integer)
calendar_minutes(m integer)

These tables would all be a single column, containing the range of values you're looking at. calendar_minutes can just have 0 and 30 as values because we're just looking at half hour increments.

This is an instance where a cross join is actually what we want.

select unix_timestamp(concat(y, '-', lpad(m, 2, '0'), '-', lpad(d, 2, '0'), ' ', lpad(h, 2, '0'), ':', lpad(mm, 2, '0'), ':', '00')) tt 
  from calendar_years
    cross join calendar_months
    cross join calendar_days
    cross join calendar_hours
    cross join calendar_minutes

Will now give us every possible combination of our table values, or in essence - every period we're interested in looking at.

We build those columns into a timestamp by using concat and lpad to turn them into a valid datetime string, and then convert it with unix_timestamp.

This gives us a set of values to left join against readed_messages, which will give us your final answer:

select from_unixtime(tt), count(id)
  from (
    select unix_timestamp(concat(y, '-', lpad(m, 2, '0'), '-', lpad(d, 2, '0'), ' ', lpad(h, 2, '0'), ':', lpad(mm, 2, '0'), ':', '00')) tt
      from calendar_years 
        cross join calendar_months
        cross join calendar_days 
        cross join calendar_hours
        cross join calendar_minutes
  ) q
  left join readed_messages rm
    on (1800 * floor(`date` / 1800)) = q.tt
  where tt <> 0  -- this method is brute force, generates some invalid dates
  group by tt

Obviously this will give us a lot of values, probably for a period far greater than you're interested in looking at, you can further filter it in the where clause if you wish.

heres a demo

Totally agree. Everything that looks difficult, awkward and weird in SQL should be done at the application level. Use the right tool for the job. — Karoly Horvath, May 27 '15 at 00:22

mysql if result does't exist return 0

1 Answers1