vendredi 8 mai 2015

How to get separate sums of last seven days from SQL with 0 value included

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.

My table called tab_leads comes with 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 - 0
  • Day 5 - 9
  • Day 6 - 15
  • Day 7 (today) - 0

At the moment I am usign this 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)

But the problem is, that if in any of those days we do not hava any data (ex. weekend) I am getting less than 7 sums. Ex:

  • Day 1 - 10
  • Day 2 - 5
  • Day 3 - 9
  • Day 4 - 15

For drawing a chart I need to have always seven sums, even with 0 value. How to do that in MySQL or MySQL + PHP?

..UPDATE: I am just trying to create SQL Fiddle withous success. Sample data:

CREATE TABLE tab_leads (
  `lead_id` int,
  `lead_create_date` timestamp
) ENGINE=InnoDB 

INSERT INTO tab_leads
  (`lead_id`, `lead_create_date`) 
VALUES
(0, '2015-05-02 05:30:40'),
(1, '2015-05-02 00:00:00'),
(2, '2015-05-03 00:00:00'),
(3, '2015-05-03 00:00:00'),
(4, '2015-05-05 00:00:00'),
(5, '2015-05-06 00:00:00'),
(6, '2015-05-07 00:00:00'),
(7, '2015-05-08 00:00:00'),
(8, '2015-05-08 00:00:00')
;

Aucun commentaire:

Enregistrer un commentaire