MySQL total amount, grouped by date

Question

I know that there have been several posts related to this, but my case is a little different, and I would like to help with this.

I need to pull some data from a database, which is the cumulative number of interactions by day. this is now what i

SELECT e.Date AS e_date, count(e.ID) AS num_interactions FROM example AS e JOIN example e1 ON e1.Date <= e.Date GROUP BY e.Date; 

The result of this is close to what I want, but not exactly what I need. The problem I am facing is dates that are stored with the hour minute and second when the interaction occurred, so the group does not group the days together.

this is what the conclusion looks like. http://screencast.com/t/N1KFNFyil on 12-23 theres 5 interactions, but its not grouped because the time stamp is different. so I need to find a way to ignore the timestamp and just look at the day.

if I try GROUP BY DAY(e.Date) , it only groups the data by day (i.e. everything that happened on the 1st of any month is grouped on one line), and the output is not what I want generally http://screencast.com/t/HN6DH3GV63M

GROUP BY DAY(e.Date), MONTH(e.Date) splits it into a month and a day of the month, but again the account is turned off.

I'm not a MySQL expert at all, so I'm puzzled by what I am missing

Answer 1

I figured out what I needed to do last night ... but since I am new to this, I couldn't post it then ... what I did was work:

 SELECT DATE(e.Date) AS e_date, count(e.ID) AS num_daily_interactions, ( SELECT COUNT(id) FROM example WHERE DATE(Date) <= e_date ) as total_interactions_per_day FROM example AS e GROUP BY e_date; 

Would it be less efficient than your request? I can just do the calculation in python after pulling the bill per day if it is more efficient because it will be on a scale of a thousand to one hundred thousand returned rows.

Answer 2

New answer

At first I did not understand that you were trying to fulfill the total amount. Here's what it would look like:

 SET @runningTotal = 0; SELECT e_date, num_interactions, @runningTotal := @runningTotal + totals.num_interactions AS runningTotal FROM (SELECT DATE(eDate) AS e_date, COUNT(*) AS num_interactions FROM example AS e GROUP BY DATE(e.Date)) totals ORDER BY e_date; 

Original answer

You may receive duplicates due to your connection. Maybe e1 has some matches for some lines that accumulate your score. Either this, or the comparison in your connection also compares seconds, which you don't expect.

Anyway, instead of chopping the datetime field into days and months, just separate the time from it. This is how you do it.

 SELECT DATE(e.Date) AS e_date, count(e.ID) AS num_interactions FROM example AS e JOIN example e1 ON DATE(e1.Date) <= DATE(e.Date) GROUP BY DATE(e.Date);