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

MySQL

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.

Best Answer

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;

Related Solutions

Mysql – Rolling count of total transactions over time

What you want is called the cumulative sum, you can do something like:

create table transactions (transactionid int, d date);
insert into transactions (transactionid, d) 
    values (1, '2014-08-04'),(2,'2014-08-05'), (3, '2014-08-18')
         , (4, '2014-08-18'), (5,'2014-08-20');

select x.y, x.w,  count(1) 
from ( 
   select distinct year(d) as y, week(d) as w 
   from transactions
) as x 
join transactions y 
    on year(y.d) < x.y
    or ( year(y.d) = x.y
     and week(y.d) <= x.w ) 
group by x.y, x.w;  

+------+------+----------+
| y    | w    | count(1) |
+------+------+----------+
| 2014 |   31 |        2 |
| 2014 |   33 |        5 |
+------+------+----------+

I did not see your additional request for 2 2 for 2014. You can do that by replacing:

select distinct year(d) as y, week(d) as w 
from transactions

...with an expression that creates the whole domain for weeks. It is often a good idea to create a calendar table that you can use to join against to get reports for missing values etc.

Thesql: counting number of tickets which are open per day basis

You can use conditional sum for this calculation something as

select 
date_format(created,'%d-%m') as created_date , 
sum( case when status='open' then 1 else 0 end )+
sum( case when status='new' then 1 else 0 end )+
sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
from table_name
where
YEAR(Created) = '2015'
group by created_date ;

Here is a test case

mysql> select * from test ;
+------+---------------------+---------------------+----------+
| id   | created             | resolved            | status   |
+------+---------------------+---------------------+----------+
|    1 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | open     |
|    2 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | new      |
|    3 | 2015-05-10 00:00:00 | 2015-05-12 00:00:00 | resolved |
|    4 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | open     |
|    5 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | new      |
|    6 | 2015-05-11 00:00:00 | 2015-05-11 00:00:00 | resolved |
+------+---------------------+---------------------+----------+
6 rows in set (0.00 sec)

mysql> select 
    -> date_format(created,'%d-%m') as created_date , 
    -> sum( case when status='open' then 1 else 0 end )+
    -> sum( case when status='new' then 1 else 0 end )+
    -> sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
    -> from test
    -> where
    -> YEAR(Created) = '2015'
    -> group by created_date ;
+--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    2 |
+--------------+------+
2 rows in set (0.00 sec)

UPDATE The count of open is a incremental as per the comment. So the following should do the trick

select
created_date,
@open:= @open+open_ticket-resolved as open
from(
 select 
 date_format(created,'%d-%m') as created_date , 
 sum( case when status='open' then 1 else 0 end )+
 sum( case when status='new' then 1 else 0 end )+
 sum( case when status='resolved' and ( date(resolved) > date(created) or date(resolved) = date(created)) then 1 else 0 end ) as open_ticket,
 sum(case when status = 'resolved' and date(resolved) = date(created) then 1 else 0 end) as resolved
 from test
 where
 YEAR(Created) = '2015'
 group by created_date
)x,(select @open:=0)r order by created_date ;

From the above sample data it will have

 +--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    5 |
+--------------+------+

UPDATE:

Count the previous tickets which got resolved on a given date and deduct that from the current day open ticket, for that you need a left join something as

select
created_date,
@open:= @open+open_ticket-resolved_ticket as open
from(
 select 
 date_format(t.created,'%d-%m') as created_date , 
 sum( case when t.status='open' then 1 else 0 end )+
 sum( case when t.status='new' then 1 else 0 end )+
 sum( case when t.status='resolved' and ( date(t.resolved) > date(created) or date(t.resolved) = date(created)) then 1 else 0 end ) as open_ticket,
 coalesce(tot,0) as resolved_ticket
 from test t left join(
    select count(*) as tot, date(resolved) as resolved from test where status='resolved' group by date(resolved)
 )x on date(x.resolved) = date(t.created) 
 where
 YEAR(t.created) = '2015'
 group by created_date
)x,(select @open:=0)r order by created_date ;

OUTPUT :

+--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    5 |
| 12-05        |    7 |
+--------------+------+

Best Answer

Related Solutions

Mysql – Rolling count of total transactions over time

Thesql: counting number of tickets which are open per day basis

Related Question