Dynamic column creation for years mysql

Question

I have written the following query for MYSQL:

SELECT DATE_FORMAT(start, '%m/%e') as start,  (@total := @total + T.id) AS TotalApps, DATE_FORMAT(start, '%Y') as year FROM (SELECT start,COUNT(*) AS id FROM application WHERE start LIKE '2016%' GROUP BY MONTH(start), DAY(start)) AS T, (SELECT @total:=0) AS n

which outputs the table as intended with a column for start date, a cumulative number of applications, and the year:

What I am trying to do is remove the LIKE '2016%' so that I can get data for all years and end up with a table that would look like:

And so on.

This question may be a duplicate of some other question, but not the one originally set. This is asking for *cumulative* sums in the columns. — Gordon Linoff, Mar 31 '16 at 02:46

Gordon Linoff · Answer 1 · 2016-04-01T02:26:40.190

1

The cumulative nature of the question makes this quite different from most other pivot questions. Here is an approach that uses separate variables for each year:

SELECT DATE_FORMAT(start, '%m/%d') as mmdd, 
       (@t2016 := @t2016 + sum(year(start) = 2016)) as tot_2016,
       (@t2015 := @t2015 + sum(year(start) = 2015)) as tot_2015,
       (@t2014 := @t2014 + sum(year(start) = 2014)) as tot_2014
FROM application a CROSS JOIN
     (SELECT @t2016 := 0, @t2015 := 0, @t2014 := 0) params
GROUP BY DATE_FORMAT(start, '%m/%d')
ORDER BY mmdd;

EDIT:

Sometimes, group by and variables don't work together. This is fixed using a subquery:

SELECT mmdd,
       (@t2016 := @t2016 + cnt_2016) as tot_2016,
       (@t2015 := @t2015 + cnt_2015) as tot_2015,
       (@t2014 := @t2014 + cnt_2014) as tot_2014
FROM (SELECT DATE_FORMAT(start, '%m/%d') as mmdd, 
             sum(year(start) = 2016)) as cnt_2016,
             sum(year(start) = 2015)) as cnt_2015,
             sum(year(start) = 2014)) as cnt_2014
      FROM application a 
      GROUP BY DATE_FORMAT(start, '%m/%d')
      ORDER BY mmdd
     ) x CROSS JOIN
     (SELECT @t2016 := 0, @t2015 := 0, @t2014 := 0) params;

edited Apr 01 '16 at 02:26

answered Mar 31 '16 at 02:50

Gordon Linoff

1,242,037
58
646
786

thanks, is there a way to accomplish it where I do not have to manually code each year? it will automaticall add rows for the years? – Steven Stangle Mar 31 '16 at 02:51
@StevenStangle . . . That requires dynamic SQL and would be even a bit more complicated because of the cumulative sum nature of the pivot. – Gordon Linoff Mar 31 '16 at 02:52
actually I ran it, and it also isnt doing the summation. Im thinking those may be the counts for the day, not cumulative correctly.http://imgur.com/eHNNixx – Steven Stangle Mar 31 '16 at 02:53
thats fine, this will be inside PHP so I can write a loop to generate the years, but the cumulative part is off. – Steven Stangle Mar 31 '16 at 03:00

score 0 · Answer 2 · answered Mar 31 '16 at 05:38

Here is a solution will a complete demo:

SELECT T2.month_day,
    IF (SUM(TotalApps * (YEAR(start) = '2016')) = 0, '', SUM(TotalApps * (YEAR(start) = '2016'))) as `2016`,
    IF (SUM(TotalApps * (YEAR(start) = '2015')) = 0, '', SUM(TotalApps * (YEAR(start) = '2015'))) as `2015`,
    IF (SUM(TotalApps * (YEAR(start) = '2014')) = 0, '', SUM(TotalApps * (YEAR(start) = '2014'))) as `2014`,
    IF (SUM(TotalApps * (YEAR(start) = '2013')) = 0, '', SUM(TotalApps * (YEAR(start) = '2013'))) as `2013`
FROM 
(    SELECT 
        start, month_day,
        IF(@last_year = YEAR(start),
           @total := @total + T.id,
           @total := T.id) AS TotalApps,
        @last_year := YEAR(start)
    FROM (SELECT start,COUNT(*) AS id, DATE_FORMAT(start, '%m/%e') as month_day
          FROM application 
          GROUP BY start
          ORDER BY start) AS T, (SELECT @total:=0, @last_year := NULL) AS n
) as T2 
GROUP BY month_day;

Here is a demo:

SQL:

-- data
create table application(start date);
insert into application values
('2016-01-01'),('2016-01-02'),('2016-01-02'),('2016-01-03'),
('2014-01-01'),('2014-01-02'),('2014-01-04'),
('2015-01-01'),('2015-01-02'),('2015-01-05');
select * from application;

-- query wanted
SELECT T2.month_day,
    IF (SUM(TotalApps * (YEAR(start) = '2016')) = 0, '', SUM(TotalApps * (YEAR(start) = '2016'))) as `2016`,
    IF (SUM(TotalApps * (YEAR(start) = '2015')) = 0, '', SUM(TotalApps * (YEAR(start) = '2015'))) as `2015`,
    IF (SUM(TotalApps * (YEAR(start) = '2014')) = 0, '', SUM(TotalApps * (YEAR(start) = '2014'))) as `2014`,
    IF (SUM(TotalApps * (YEAR(start) = '2013')) = 0, '', SUM(TotalApps * (YEAR(start) = '2013'))) as `2013`
FROM 
(    SELECT 
        start, month_day,
        IF(@last_year = YEAR(start),
           @total := @total + T.id,
           @total := T.id) AS TotalApps,
        @last_year := YEAR(start)
    FROM (SELECT start,COUNT(*) AS id, DATE_FORMAT(start, '%m/%e') as month_day
          FROM application 
          GROUP BY start
          ORDER BY start) AS T, (SELECT @total:=0, @last_year := NULL) AS n
) as T2 
GROUP BY month_day
;

Output:

mysql> select * from application;
+------------+
| start      |
+------------+
| 2016-01-01 |
| 2016-01-02 |
| 2016-01-02 |
| 2016-01-03 |
| 2014-01-01 |
| 2014-01-02 |
| 2014-01-04 |
| 2015-01-01 |
| 2015-01-02 |
| 2015-01-05 |
+------------+
10 rows in set (0.00 sec)

mysql> -- query wanted
mysql> SELECT T2.month_day,
    ->     IF (SUM(TotalApps * (YEAR(start) = '2016')) = 0, '', SUM(TotalApps * (YEAR(start) = '2016'))) as `2016`,
    ->     IF (SUM(TotalApps * (YEAR(start) = '2015')) = 0, '', SUM(TotalApps * (YEAR(start) = '2015'))) as `2015`,
    ->     IF (SUM(TotalApps * (YEAR(start) = '2014')) = 0, '', SUM(TotalApps * (YEAR(start) = '2014'))) as `2014`,
    ->     IF (SUM(TotalApps * (YEAR(start) = '2013')) = 0, '', SUM(TotalApps * (YEAR(start) = '2013'))) as `2013`
    -> FROM
    -> (    SELECT
    ->         start, month_day,
    ->         IF(@last_year = YEAR(start),
    ->            @total := @total + T.id,
    ->            @total := T.id) AS TotalApps,
    ->         @last_year := YEAR(start)
    ->     FROM (SELECT start,COUNT(*) AS id, DATE_FORMAT(start, '%m/%e') as month_day
    ->           FROM application
    ->           GROUP BY start
    ->           ORDER BY start) AS T, (SELECT @total:=0, @last_year := NULL) AS n
    -> ) as T2
    -> GROUP BY month_day
    -> ;
+-----------+------+------+------+------+
| month_day | 2016 | 2015 | 2014 | 2013 |
+-----------+------+------+------+------+
| 01/1      | 1    | 1    | 1    |      |
| 01/2      | 3    | 2    | 2    |      |
| 01/3      | 4    |      |      |      |
| 01/4      |      |      | 3    |      |
| 01/5      |      | 3    |      |      |
+-----------+------+------+------+------+
5 rows in set (0.00 sec)

Dynamic column creation for years mysql

2 Answers2

SQL:

Output: