MySQL to get the number of rows that fall on a date for each day of the month

Question

I have a table containing a list of community events with columns on the days when the event starts and ends. If the end date is 0, the event only occurs on the start day. I have a query that returns the number of events occurring any day:

SELECT COUNT(*) FROM p_community e WHERE (TO_DAYS(e.date_ends)=0 AND DATE(e.date_starts)=DATE('2009-05-13')) OR (DATE('2009-05-13')>=DATE(e.date_starts) AND DATE('2009-05-13')<=DATE(e.date_ends)) 

I just add on any date that I want to check for "2009-05-13".

I need to be able to get this data every day for the whole month. I could just run a query against each day one at a time, but I would prefer to run one query, which can give me a whole month at once. Does anyone have any suggestions on how I can do this?

And no, I cannot use the stored procedure.

Answer 1

Try:

 SELECT COUNT(*), DATE(date) FROM table WHERE DATE(dtCreatedAt) >= DATE('2009-03-01') AND DATE(dtCreatedAt) <= DATE('2009-03-10') GROUP BY DATE(date); 

This will receive the amount for each day in May 2009.

UPDATED: Now works on a range of dates spanning months / years.

Answer 2

Unfortunately, MySQL not able to generate a rowset of a given number of rows.

You can create a helper table:

 CREATE TABLE t_day (day INT NOT NULL PRIMARY KEY) INSERT INTO t_day (day) VALUES (1), (2), …, (31) 

and use it in JOIN :

 SELECT day, COUNT(*) FROM t_day JOIN p_community e ON day BETWEEN DATE(e.start) AND IF(DATE(e.end), DATE(e.end), DATE(e.start)) GROUP BY day 

Or you can use the ugly subquery:

 SELECT day, COUNT(*) FROM ( SELECT 1 AS day UNION ALL SELECT 2 AS day … UNION ALL SELECT 31 AS day ) t_day JOIN p_community e ON day BETWEEN DATE(e.start) AND IF(DATE(e.end), DATE(e.end), DATE(e.start)) GROUP BY day