0

So I'm counting articles per year/month between the start of the year and the current time:

SELECT Year(FROM_UNIXTIME(date)) as year
     , Month(FROM_UNIXTIME(date)) as month
     , Count(*) as `total` 
  FROM articles 
 WHERE date BETWEEN UNIX_TIMESTAMP(DATE('2017-01-01 00:00:00')) AND UNIX_TIMESTAMP(DATE('2017-05-17 12:00:05')) 
 GROUP 
    BY Year(FROM_UNIXTIME(date))
     , Month(FROM_UNIXTIME(date))

The only issue, is that months that have zero, won't show up.

Is there an easy way around it?

Strawberry
  • 33,750
  • 13
  • 40
  • 57
NaughtySquid
  • 1,947
  • 3
  • 29
  • 44

1 Answers1

0

The best solution I can think of is to do an inner join with a lookup table that has months 1-12 in them. Thus verifying there will always be a 12 month result set? Possibly including a restriction for the current date month can not be surpassed, so you don't actually always get for the whole year.

look here: Include missing months in Group By query

Community
  • 1
  • 1
Morten Bork
  • 1,413
  • 11
  • 23
  • I suspect you meant "outer join", as an inner join would not return rows from the "months" table which didn't have a match in the grouped data. – Bob Jarvis - Слава Україні May 17 '17 at 12:17
  • Ahm no? Not if you use the hardcoded list as the left table? Then the left data would be dominant? Or am I confused out of my mind? (I am down with the flu today, so I am not ruling out I am confused at all!) But the fellows in the link, are much more on point! just have a look, they have examples that should guide you just fine. – Morten Bork May 17 '17 at 12:20
  • 1
    An inner join won't return anything unless there's data on both sides of the join. And I did look at the link - a LEFT OUTER JOIN is used. – Bob Jarvis - Слава Україні May 17 '17 at 15:19
  • You are of course right. Sorry. (You could likely do something silly like a case if then else end, and make sure that something would be present, but as you rightly point out, just make it an outer). In my defence. I am sick ;) – Morten Bork May 17 '17 at 16:31
  • That's OK - not a problem. I hope you feel better soon. – Bob Jarvis - Слава Україні May 17 '17 at 19:30