Total Records For Each Day (Including Days with No Records)

partypete

Hello,

I have a site and I'd like to visually track the number of new members per day using a graph like a stats program.

Here is an image from a stats program that I'd like to sort of duplicate🙁yellow bars only)

How would I query MySQL so that it will add up the number of records for each date of a given time frame, and return the totals with each totals corresponding date?

So if I searched the database for new members between 2004-11-01 and 2004-12-01 to get new members from this month I'd first get this:

2004-11-03
2004-11-03
2004-11-05
2004-11-13
2004-11-13
2004-11-13
2004-11-21

But I'd like the query to return this:

2004-11-03 2
2004-11-05 1
2004-11-13 3
2004-11-21 1

END OF PRIMARY(MOST IMPORTANT) QUESTION

Bonus Question would be:

Would it also be possible to insert 0 for dates that don't appear in the database for the given time frame?(To display a true number of days instead of just days that members actually joined...)

So the output would now be:

2004-11-01 0
2004-11-02 0
2004-11-03 2
2004-11-04 0
2004-11-05 1
2004-11-06 0
2004-11-07 0
2004-11-08 0
2004-11-09 0
2004-11-10 0
2004-11-11 0
2004-11-12 0
2004-11-13 3
...etc......

Thank you very much for your help!

partypete

Ok, I actually figured out how to achieve my first question with this(simplified version):

SELECT COUNT(user_name), DATE_FORMAT(user_joined,'%Y-%m-%d') AS the_date
FROM users
GROUP BY the_date

This gives me the total records for each day. But can anyone tell me how to insert the dates that don't have any records and give them a total of "0"?

Thanks!

mourisj

select date_visit, count(date_visit)
from tablename
where ...
group by date_visit
order by date_visit

JJ Mouris

P.S. Sorry, this is a solution for part 1, not for part 2 of your quesiotn, but I see thtat you found it by yourselves.

Sxooter

What you want is a "pivot" table. Create a table with ALL the dates ranged in it that you want. Then left join on that table, so you have each date, and if there are no matching records in your real table, you'll just get the date with nulls for the rest of the record.