MySQL: summary query for date activity by last 7 days, week and all year totals

Question

I have a customer table with records like

+-----+-------+-------------+-----------+
| ID  | Name  | Date        |  Status   |
+-----+-------+-------------+-----------+
| 1   | Joe   | 2014.02.01  | New       |
| 2   | Bob   | 2014.02.02  | Requested |
| 3   | Rob   | 2014.02.02  | New       |
| 3   | Dale  | 2014.02.02  | New       |
| 4   | Jane  | 2014.02.03  | New       |
+-----+-------+-------------+-----------+

... assume data going back in time here

Using a "Group By" query I can easily get a result like the following:

SELECT Status, Date, Count(*) FROM Customer GROUP BY Status, Date

+------------+-------------+-------+
| Status     | Date        | Count |
+------------+-------------+-------+
| New        | 2014.02.01  |     1 |
| New        | 2014.02.02  |     2 |
| New        | 2014.02.03  |     1 |
| Requested  | 2014.02.02  |     1 |
+------------+-------------+-------+

I want to also add rows for week total and a year to date total, so something like the following:

+------------+-------------+-------+
| Status     | Date        | Count |
+------------+-------------+-------+
| New        | 2014.02.01  |     1 |
| New        | 2014.02.02  |     2 |
| New        | 2014.02.03  |     1 |
| New        | Week1       |     4 |
| New        | YearToDate  |   500 |
| Requested  | 2014.01.02  |     1 |
| Requested  | Week1       |     1 |
| Requested  | yearToDate  |   200 |
+------------+-------------+-------+

The "Week1" would be just the total of the previous 7 days

The "YearToDate" would be just the total of that group since the start of the year.

I'd ideally like to do this with a single query. However I don't know how. My other alternative is to write 3 queries:

a) Select last 7 days and group by day of week

b) Select last 7 days and group by week in year

c) Select since beginning of year and group by

And then mash them together in my program (not my ideal solution).

Any help is appreciated.

Answer 1

SELECT * FROM
(
    Status,Date, Count(*) RowCount FROM Customer
    WHERE Date > (DATE(NOW()) - INTERVAL 7 DAY)
    GROUP BY Status,Date
    UNION
    Status,WEEK(Date) WK,Count(*) RowCount FROM Customer
    WHERE Date >= MAKEDATE(YEAR(NOW()),1)
    GROUP BY Status,WK
    UNION
    Status,'YearToDate' YTD,Count(*) RowCount FROM Customer
    WHERE Date >= MAKEDATE(YEAR(NOW()),1)
    GROUP BY Status,YTD
) A ORDER BY Status,Date;