I have a reports table with the following structure :
I want a SQL Query to get the report for the last 365 days by following conditions :
- Group dates if the same date is repeated.
- The days which the report is not available for the last 365 days, I need those days added to the result rows with 0 as their success and failed recipients.
I tried to get it by group by report dates
SELECT report_date, SUM(success_recipient) as success_recipient, SUM(failed_recipient) as failed_recipient FROM reports GROUP BY report_date;
and I have got the grouped result which satisfies the first condition
Now I need to append the rest of the days in the last 365 days to this result in which 0 as their success and failure recipients.
Expected result :
MYSQL VERSION : 5.6
Advertisement
Answer
From the above comments and the answer, I could write this query which gave me the expected outcome :
SELECT a.date, SUM(COALESCE(r.success_recipient, 0)), SUM(COALESCE(r.failed_recipient, 0)) FROM ( SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY AS date FROM (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d ) a LEFT JOIN reports r ON a.date = r.report_date WHERE a.date between DATE_SUB(CURDATE(), INTERVAL 1 YEAR) and now() GROUP BY a.date;