1

I am trying to make a graph that has a point for each day showing the number of horses present per-day.

This is example of data I have (MySQL)

horse_id   |   start_date   |   end_date    |
1          |   2011-04-02   |   2011-04-03  |
2          |   2011-04-02   |   NULL        |
3          |   2011-04-04   |   2014-07-20  |
4          |   2012-05-11   |   NULL

So a graph on that data should output one row per day starting on 2011-04-02 and ending on CURDATE, for each day it should return how many horses are registered.

I can't quite wrap my head around how I would do this, since I only have a start date and an end date for each item, and I want to know per-day how many was present on that day.

Right now, I do a loop and a SQL query per day, but that is - as you might have guesses - thousands of queries, and I was hoping it could be done smarter.

If a day between 2011-04-02 and now contains nothing, I still want it out but with a 0.

If possible I would like to avoid having a table with a row for each day containing a count.

I hope it makes sense, I am very stuck here.

Mark
  • 13
  • 3
  • I'm not sure that MySQL is the best graphing software. Be fun to try though! I mean, clearly, it *can* be done - just not sure that it should! http://explainextended.com/2012/12/31/happy-new-year-4/#more-5492 – Strawberry Jul 30 '14 at 10:15
  • possible duplicate of [Get a list of dates between two dates](http://stackoverflow.com/questions/510012/get-a-list-of-dates-between-two-dates) – Neil Jul 30 '14 at 10:18
  • @Strawberry, the graph is to be made with data from mysql.. Not by mysql :) – Mark Jul 30 '14 at 10:30
  • @Neil, it is not a duplicate of that. I am trying to achieve something different :) – Mark Jul 30 '14 at 10:30
  • So, that thing that you use to make the graph - what is that? Maybe that's where you should be doing the processing? In other words, all that's really wrong with your present set up is that you're doing a query per day - just do a query for all dates and handle missing dates at the application level. – Strawberry Jul 30 '14 at 10:57
  • I don't think you understand the question correctly. See the answer fancyPants made, it seems like what i want. – Mark Jul 30 '14 at 11:05

1 Answers1

0

What you should have, is a table containing just dates from at least the earliest date in your current table till the current date.

Then you can use this table to left join it something like this:

SELECT
dt.date,
COUNT(yt.horse_id)
FROM
dates_table dt
LEFT JOIN your_table yt ON dt.date BETWEEN yt.start_date AND COALESCE(end_date, CURDATE())
GROUP BY dt.date

Be sure to have a column of your_table in the COUNT() function, otherwise it counts the NULL values too.

The COALESCE() function returns the first of its parameter which isn't NULL, so if you don't have an end_date specified, the current date is taken instead.

fancyPants
  • 50,732
  • 33
  • 89
  • 96