Search code examples
sqlmysqldateaggregate-functions

How to retrieve sum of data form last 7 days as a separate sums

In my CRM system I have table with leads. I would like to make a chart to see how many leads were added in last 7 days. For that purpose I need to have separete sums for every day from last week.

How to do that in MySQL?

My table called tab_leads it have lead_id (integer) and lead_create_date (time stamp, format: 0000-00-00 00:00:00)

So I need something like:

  • Day 1 - 10
  • Day 2 - 0
  • Day 3 - 5
  • Day 4 - 1
  • Day 5 - 9
  • Day 6 - 15
  • Day 7 (today) - 2
Solution

  • Just use a GROUP BY query:

    SELECT
    DATE(lead_create_date) AS `Date`,
    COUNT(*) AS `Leads`
FROM
    tab_leads
WHERE
    lead_create_date >=  CURRENT_DATE - INTERVAL 6 DAY
GROUP BY
    DATE(lead_create_date)

    The above query assumes that there are no future records and current day is counted as the 7th day.