[Solved] Mysql show data in Pivot View

by


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.

1

solved Mysql show data in Pivot View