1

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
Salman A
  • 262,204
  • 82
  • 430
  • 521
Tikky
  • 1,253
  • 2
  • 17
  • 36

3 Answers3

6

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.

Salman A
  • 262,204
  • 82
  • 430
  • 521
  • Hello, thank you for the info. This works, but when in any of those day wasn't data (no new leads), I am not getting data. I mean I need always 7 sums, even if sum=0. Any chances to get this type of result? – Tikky Feb 10 '15 at 10:22
  • 1
    Oh. I would use a table of dates (or something like this http://stackoverflow.com/q/510012/87015) and left join it with the leads table. – Salman A Feb 10 '15 at 10:27
1

Try this Mysql Query

SELECT * FROM tab_leads WHERE DATE(lead_create_date) = DATE_SUB(DATE(NOW()), INTERVAL 7 DAY) GROUP BY DATE(lead_create_date);
Arun
  • 750
  • 5
  • 12
  • This works, but also it's not showing days with empty data. I need always seven sums, even if sum=0. – Tikky Feb 10 '15 at 14:11
1

Try this 

SELECT COUNT(ead_id) from tab_leads GROUP BY DAY(lead_create_date)

( or as per your requirement )

SELECT SUM(ead_id) from tab_leads GROUP BY DAY(lead_create_date)
Chintan Gor
  • 1,062
  • 2
  • 15
  • 35
  • Grouping by day will produce incorrect results. For example, all leads for Jan-1, Feb-1, Mar-1, etc will be consolidated in one row. – Salman A Feb 10 '15 at 10:25