MySQL: Multiple Running Totals from Different Subqueries -
when run single query using following formula have first column give month/year, second give number of people signing per month, , third give running total of signers, works great:
set @runtot1:=0; select 1rt.month, 1rt.1signed, (@runtot1 := @runtot1 + 1rt.1signed) 1rt (select date_format(str_to_date(s.datecontacted,'%m/%d/%y'),'%y-%m') month, ifnull(count(distinct case when s.surveyid = 791796 s.id else null end),0) 1signed table1 s join table2 m on s.id = m.id , m.current = "yes" str_to_date(s.datecontacted,'%m/%d/%y') > '2015-03-01' group month order month) 1rt with query above, following results table, want if needed count 1 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 can't figure out how multiple subqueries since need happen multiple columns @ same time. example, attempting things (which doesn't work):
set @runtot1:=0; set @runtot2:=0; select date_format(str_to_date(s1.datecontacted,'%m/%d/%y'),'%y-%m') month, t1.1signed, (@runtot1 := @runtot1 + t1.1signed) 1rt, t2.2signed, (@runtot2 := @runtot2 + t2.2signed) 2rt (select date_format(str_to_date(s.datecontacted,'%m/%d/%y'),'%y-%m') month, ifnull(count(distinct case when s.surveyid = 791796 s.id else null end),0) 1signed table1 s left join table2 m on m.id = s.id m.current = "yes" group month order month) t1, (select date_format(str_to_date(s.datecontacted,'%m/%d/%y'),'%y-%m') month, ifnull(count(distinct case when s.surveyid = 846346 s.id else null end),0) 2signed table1 s left join table2 m on m.id = s.id m.current = "yes" group month order month) t2, table1 s1 left join table2 m1 on m1.id = s1.id , m1.current = "yes" str_to_date(s1.datecontacted,'%m/%d/%y') > '2015-03-01' group date_format(str_to_date(s1.datecontacted,'%m/%d/%y'),'%y-%m') order date_format(str_to_date(s1.datecontacted,'%m/%d/%y'),'%y-%m') that blew results badly -- tried left joins 2 next each other, didn't work either.
here's sql fiddle few values query @ top works, not query needed idea below.
if multiple subquery version of code worked, below 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 figure out way counts month , rolling totals since march 2015 2 different survey questions using same query. appreciated!
it seems you're after 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 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 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 ( select date_format(date_contacted,'%y-%m') month , sum(survey_id = 791796) , sum(survey_id = 846346) b table1 x join table2 y on y.id = x.id y.is_current = 1 group month ) x join (select @a:=0,@b:=0) vars order month; +---------+------+------+------+------+ | month | | 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 | +---------+------+------+------+------+ 
Comments
Post a Comment