Sorry Guys, could not generate a fiddle since facing some issues with sqlfiddle.com
I have a table in mysql database which is as follows:
Username mailtime mailid
User4 3/23/2013 10:26 4473
User1 4/6/2013 16:13 10934
User4 4/6/2013 17:17 10957
User1 4/6/2013 23:56 11092
User2 4/7/2013 11:58 11187
User1 4/7/2013 12:06 11190
User4 4/7/2013 13:11 11216
User4 4/7/2013 13:14 11217
User1 4/7/2013 14:40 11245
User5 4/7/2013 15:52 11259
User1 4/7/2013 18:12 11303
User5 4/7/2013 19:56 11323
User1 4/7/2013 22:52 11358
User4 4/8/2013 11:13 11465
User1 4/8/2013 11:20 11475
User1 4/8/2013 11:35 11491
User4 4/8/2013 12:10 11511
User4 4/8/2013 12:38 11532
User4 4/8/2013 12:51 11540
User4 4/8/2013 13:06 11551
User1 4/8/2013 13:09 11552
User4 4/8/2013 13:15 11560
User1 4/8/2013 13:24 11572
User1 4/8/2013 14:01 11614
User4 4/8/2013 14:27 11640
User1 4/8/2013 15:41 11700
User5 4/8/2013 16:04 11730
User1 4/8/2013 17:40 11814
User4 4/9/2013 11:16 12117
User1 4/9/2013 12:41 12198
User1 4/9/2013 12:59 12209
User4 4/9/2013 13:58 12243
User4 4/9/2013 14:05 12250
User1 4/9/2013 14:15 12256
User4 4/9/2013 16:51 12351
User1 4/9/2013 17:33 12397
User1 4/9/2013 19:01 12455
User4 4/9/2013 19:15 12463
User5 4/9/2013 20:59 12517
User1 4/9/2013 21:26 12530
User1 4/9/2013 22:46 12561
User1 4/10/2013 1:01 12595
User1 4/10/2013 8:42 12631
User1 4/10/2013 10:18 12663
User1 4/10/2013 11:21 12697
User3 4/10/2013 11:27 12701
User4 4/10/2013 11:34 12705
User1 4/10/2013 15:26 12856
User4 4/10/2013 16:51 12909
User2 4/10/2013 16:53 12913
The output that i require is as follows:
Username < 5 days 6-Apr 7-Apr 8-Apr 9-Apr 10-Apr Grand Total
User1
User2
User3
User4
User5
Grand Total
Not sure how to do this.
The max mailtime is displayed in the 2nd last column and previous days in the previous columns and < 5 days is cumilative of all the other days
EDIT::
The data that is required is the count of mailids
The columns < 5 days, 6-Apr, 7-Apr, 8-Apr, 9-Apr and 10-Apr are the columns from mailtime Column. If the max date from the column is say 25th Mar, the the columns should be < 5 days, 20-Mar, 21-Mar, 22-Mar, 23-Mar, 24 Mar and 25-Mar
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.
