MySQL cumulative sum grouped by date

by

New Answer

At first, I didn’t understand you were trying to do a running total. Here is how that would look:

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 could be getting duplicates because of your join. Maybe e1 has more than one match for some rows which is inflating your count. Either that or the comparison in your join is also comparing the seconds, which is not what you expect.

Anyhow, instead of chopping the datetime field into days and months, just strip the time from it. Here is how you do that.

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);

More Related Contents:

  1. MySQL Query GROUP BY day / month / year
  2. Select all months within given date span, including the ones with 0 values
  3. Left Outer Join doesn’t return all rows from my left table?
  4. How to convert a string to date in MySQL?
  5. Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs
  6. How to get the number of days of difference between two dates on MySQL?
  7. MySQL: Select All Dates In a Range Even If No Records Present
  8. MySQL – Selecting a Column not in Group By
  9. Mysql query to dynamically convert rows to columns
  10. CURRENT_DATE/CURDATE() not working as default DATE value
  11. Select the 3 most recent records where the values of one column are distinct
  12. Is there ANY_VALUE capability for mysql 5.6?
  13. Cumulative sum over a set of rows in mysql
  14. Get the latest date from grouped MySQL data
  15. In SQL, how to select the top 2 rows for each group
  16. SQL Query to get column values that correspond with MAX value of another column?
  17. Returning the ‘last’ row of each ‘group by’ in MySQL
  18. Count days between two dates, excluding weekends (MySQL only)
  19. How to select the most recent set of dated records from a mysql table
  20. How do I get the first day of the week of a date in mysql?
  21. MySQL: How do I join same table multiple times?
  22. MySQL date format
  23. Converting SELECT DISTINCT ON queries from Postgresql to MySQL
  24. mysql select sum group by date
  25. #1292 – Incorrect date value: ‘0000-00-00’ [duplicate]
  26. MySQL Group By Hours
  27. SQL date format convert? [dd.mm.yy to YYYY-MM-DD]
  28. MySQL: Find Missing Dates Between a Date Range
  29. SQL query with avg and group by
  30. How do I extract Month and Year in a MySQL date and compare them?

Leave a Comment