Your description of what you want for the desired the result is not exactly clear but it seems like you can use the following to get the result. This get the total number of rows per username on each of the previous 5 days (based on the max date) as well as the total number of rows for each user before this 5 day period:
select
coalesce(username, 'Grand Total') username,
max(`< 5 days`) `< 5 days`,
sum(case when maildate = '6-Apr' then 1 else 0 end) `6-Apr`,
sum(case when maildate = '7-Apr' then 1 else 0 end) `7-Apr`,
sum(case when maildate = '8-Apr' then 1 else 0 end) `8-Apr`,
sum(case when maildate = '9-Apr' then 1 else 0 end) `9-Apr`,
sum(case when maildate = '10-Apr' then 1 else 0 end) `10-Apr`,
count(*) GrandTotal
from
(
select c.username,
date_format(c.mailtime, '%e-%b') maildate,
coalesce(o.`< 5 days`, 0) `< 5 days`
from yt c
left join
(
select username,
count(*) `< 5 days`
from yt
where mailtime <= (select date_sub(max(mailtime), interval 4 DAY)
from yt)
) o
on c.username = o.username
where c.mailtime >= (select date_sub(max(mailtime), interval 4 DAY)
from yt)
) d
group by username with rollup;
See SQL Fiddle with Demo.
I wrote a hard-coded version so you could see how the code with be written but if you are basing the data off the max(mailtime) then you will most likely want to use dynamic SQL to get the result. You can use a prepared statement to generate the SQL string that will be executed:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'sum(CASE WHEN maildate = ''',
date_format(mailtime, '%e-%b'),
''' THEN 1 else 0 END) AS `',
date_format(mailtime, '%e-%b'), '`'
)
) INTO @sql
FROM yt
WHERE mailtime >= (select date_sub(max(mailtime), interval 4 DAY)
from yt);
SET @sql
= CONCAT('SELECT coalesce(username, ''Grand Total'') username,
max(`< 5 days`) `< 5 days`, ', @sql, ' ,
count(*) GrandTotal
from
(
select c.username,
date_format(c.mailtime, ''%e-%b'') maildate,
coalesce(o.`< 5 days`, 0) `< 5 days`
from yt c
left join
(
select username,
count(*) `< 5 days`
from yt
where mailtime <= (select date_sub(max(mailtime), interval 4 DAY)
from yt)
) o
on c.username = o.username
where c.mailtime >= (select date_sub(max(mailtime), interval 4 DAY)
from yt)
) d
group by username with rollup ');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
See SQL Fiddle with Demo. Both queries give the result:
| USERNAME | < 5 DAYS | 6-APR | 7-APR | 8-APR | 9-APR | 10-APR | GRANDTOTAL |
--------------------------------------------------------------------------------
| User1 | 0 | 1 | 4 | 7 | 7 | 5 | 24 |
| User2 | 0 | 0 | 1 | 0 | 0 | 1 | 2 |
| User3 | 0 | 0 | 0 | 0 | 0 | 1 | 1 |
| User4 | 2 | 1 | 2 | 7 | 5 | 2 | 17 |
| User5 | 0 | 0 | 2 | 1 | 1 | 0 | 4 |
| Grand Total | 2 | 2 | 9 | 15 | 13 | 9 | 48 |
If this not the result that you want, then you will have to further explain your need.
