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