0

I am trying to achieve something that looks like a graph like data. I need to get a date-grouped data from a table as such as I can see count of data as at a particular date. Need it in order to build a graph and be able to see the upward movement of data from day to day. Currently my query provides count for just that date. This is my current query:

SELECT DATE_FORMAT(p.created_at, '%Y-%m-%d') AS 'day', COUNT(p.id) AS 'count'
FROM properties AS p
WHERE p.created_at BETWEEN SUBDATE(NOW(), 7) AND NOW()
GROUP BY DATE_FORMAT(p.created_at, '%Y-%m-%d')
ORDER BY 'day' DESC

The above query results in something like this:

day          count
2021-11-12   3
2021-11-15   5
2021-11-16   4

This means that on 2021-11-12, 3 properties were added to the table.. 5 properties on 2021-11-15 and so on. What I wish to achieve is to get the total count of properties as of each date. Lets say the total count of the whole properties as at 2021-11-16 were 100, then 2021-11-16 should show 100 as its count, 2021-11-15 should show 96 and 2021-11-12 shows 91.

Please note that I am not querying the whole data instead I am query for particular date range like in the above query which is 7 days from the current date. Using a cumulative like this did not get the correct data as it starts calculating from my start date instead of the whole data count.

emmaakachukwu
  • 515
  • 1
  • 7
  • 16
  • did you check **How to Calculate Cumulative Sum-Running Total in MySQL** [here](https://popsql.com/learn-sql/mysql/how-to-calculate-cumulative-sum-running-total-in-mysql) – Bart Nov 17 '21 at 20:56
  • My issue is not quite cummulative as I'm not querying for the whole dates.. I'm querying for some particular sub dates – emmaakachukwu Nov 17 '21 at 21:08
  • Depends your explanation at the end of the question you should get total count and decrease it cumulatively row by row, like the link I mentioned, you can do that with sum function with multiply -1 the count value. – Bart Nov 17 '21 at 21:26

0 Answers0