MySQL: Multiple Running Totals from Different Subqueries

Question

When I run a single query using the following formula to have the first column give back the month/year, the second give back the number of people signing per month, and the third give back the running total of signers, it works great:

SET @runtot1:=0;
SELECT
   1rt.MONTH,
   1rt.1signed,
   (@runtot1 := @runtot1 + 1rt.1signed) AS 1rt
FROM 
   (SELECT
       DATE_FORMAT(STR_TO_DATE(s.datecontacted,'%m/%d/%Y'),'%Y-%m') AS MONTH,
       IFNULL(COUNT(DISTINCT CASE WHEN s.surveyid = 791796 THEN s.id ELSE NULL END),0) AS 1signed
    FROM  table1 s
    JOIN table2 m ON s.id = m.id AND m.current = "Yes"
    WHERE STR_TO_DATE(s.datecontacted,'%m/%d/%Y') > '2015-03-01'
    GROUP  BY MONTH
    ORDER  BY MONTH) AS 1rt

With the query above, I get the following results table, which would be exactly what I want if I only needed to count one thing:

MONTH   1signed 1rt
2015-03 0       0
2015-04 1       1
2015-05 0       1
2015-08 1       2
2015-10 1       3
2015-11 1       4
2016-01 0       4
2016-02 0       4

But I can't figure out how to do that with multiple subqueries since I need this to happen for multiple columns at the same time. For example, I was attempting things like this (which doesn't work):

SET @runtot1:=0;
SET @runtot2:=0;
select 
  DATE_FORMAT(STR_TO_DATE(s1.datecontacted,'%m/%d/%Y'),'%Y-%m') AS MONTH,
  t1.1signed,
  (@runtot1 := @runtot1 + t1.1signed) AS 1rt,
  t2.2signed,
  (@runtot2 := @runtot2 + t2.2signed) AS 2rt
from
    (select 
     DATE_FORMAT(STR_TO_DATE(s.datecontacted,'%m/%d/%Y'),'%Y-%m') AS MONTH,
     IFNULL(COUNT(DISTINCT CASE WHEN s.surveyid = 791796 THEN s.id ELSE NULL END),0) AS 1signed
     from table1 s 
     left join table2 m ON m.id = s.id
     where m.current = "Yes"
     GROUP BY MONTH
     ORDER BY MONTH) as T1,
     (select 
     DATE_FORMAT(STR_TO_DATE(s.datecontacted,'%m/%d/%Y'),'%Y-%m') AS MONTH,
     IFNULL(COUNT(DISTINCT CASE WHEN s.surveyid = 846346 THEN s.id ELSE NULL END),0) AS 2signed
     from table1 s 
     left join table2 m ON m.id = s.id
     where m.current = "Yes"
     GROUP BY MONTH
     ORDER BY MONTH) as T2,
     table1 s1
LEFT JOIN table2 m1 ON m1.id = s1.id AND m1.current = "Yes"
WHERE STR_TO_DATE(s1.datecontacted,'%m/%d/%Y') > '2015-03-01'
GROUP BY DATE_FORMAT(STR_TO_DATE(s1.datecontacted,'%m/%d/%Y'),'%Y-%m')
ORDER BY DATE_FORMAT(STR_TO_DATE(s1.datecontacted,'%m/%d/%Y'),'%Y-%m')

That blew up my results badly -- I also tried LEFT JOINs to get those two next each other, but that didn't work either.

Here's a SQL Fiddle with a few values with the query at the top that works, but not the query needed to look like the idea below.

If the multiple subquery version of the code worked, below would be the ideal end-result:

MONTH   1signed 1rt 2signed 2rt
2015-03 0       0   1       1
2015-04 1       1   0       1
2015-05 0       1   1       2
2015-08 1       2   0       2
2015-10 1       3   0       2
2015-11 1       4   0       2
2016-01 0       4   0       2
2016-02 0       4   1       3

Just trying to figure out a way to get counts by month and rolling totals since March 2015 for two different survey questions using the same query. Any help would be greatly appreciated!

Strawberry · Accepted Answer

It seems that you're after something like this...

The data set:

DROP TABLE IF EXISTS table1;

CREATE TABLE table1
( id INT NOT NULL
, date_contacted DATE NOT NULL
, survey_id INT NOT NULL
, PRIMARY KEY(id,survey_id)
);

DROP TABLE IF EXISTS table2;

CREATE TABLE table2
(id INT NOT NULL PRIMARY KEY
,is_current TINYINT NOT NULL DEFAULT 0
);

INSERT INTO table1 VALUES
(1,"2015-03-05",846346),
(2,"2015-04-15",791796),
(2,"2015-05-04",846346),
(3,"2015-06-07",791796),
(3,"2015-06-08",846346),
(4,"2015-08-02",791796),
(5,"2015-10-15",791796),
(6,"2015-11-25",791796),
(6,"2016-01-02", 11235),
(6,"2016-02-06",846346);

INSERT INTO table2 (id,is_current) VALUES
(1,1),
(2,1),
(3,0),
(4,1),
(5,1),
(6,1);

The query:

SELECT x.*
     , @a:=@a+a rt_a
     , @b:=@b+b rt_b
  FROM 
     ( SELECT DATE_FORMAT(date_contacted,'%Y-%m') month
            , SUM(survey_id = 791796) a
            , SUM(survey_id = 846346) b
         FROM table1 x 
         JOIN table2 y 
           ON y.id = x.id 
        WHERE y.is_current = 1
        GROUP 
           BY month
     ) x
  JOIN (SELECT @a:=0,@b:=0) vars
 ORDER 
    BY month;
+---------+------+------+------+------+
| month   | a    | b    | rt_a | rt_b |
+---------+------+------+------+------+
| 2015-03 |    0 |    1 |    0 |    1 |
| 2015-04 |    1 |    0 |    1 |    1 |
| 2015-05 |    0 |    1 |    1 |    2 |
| 2015-08 |    1 |    0 |    2 |    2 |
| 2015-10 |    1 |    0 |    3 |    2 |
| 2015-11 |    1 |    0 |    4 |    2 |
| 2016-01 |    0 |    0 |    4 |    2 |
| 2016-02 |    0 |    1 |    4 |    3 |
+---------+------+------+------+------+

MySQL: Multiple Running Totals from Different Subqueries

Answers (2)

Related Questions