i have 2 tables
table 1 table 2 id q_id content id w_id q_id c_id ranking ---------------------- ------------------------------------------------ 95 2046 1=e 123 22404 2046 100 0 96 2046 2=g 124 22404 2046 101 1 97 2046 3=n 125 22404 2046 102 1 98 2046 4=b 126 22404 2046 103 1 99 2046 5=v 127 22404 2046 104 2 100 2046 a1 128 22404 2046 105 2 101 2046 a2 129 22505 2046 a1 0 102 2046 a3 130 22505 2046 a2 2 103 2046 a4 131 22505 2046 a3 1 104 2046 a5 132 22505 2046 a4 2 105 2046 a6 133 22505 2046 a5 3 106 2046 a7 134 22505 2046 a6 3 ----------------------- 135 22505 2046 a7 0 i want execut format(sd didn't calculate here)
id content av total e g n b v sd ---------------------------------------------------------------------- 100 a1 1 2 2 0 0 0 0 0 101 a2 1.5 2 0 1 1 0 0 0 102 a3 1 2 1 1 0 0 0 0 103 a4 2 2 0 2 0 0 0 0 104 a5 3 2 0 0 2 0 0 0 105 a6 3 2 0 0 2 0 0 0 106 a7 3 1 0 0 1 0 0 0 the code modified base on mark's suggestion:
select * table_1 t1 join table_2 t2 on t2.c_id in (t1.id, t1.content) t1.q_id = 2046 , t2.q_id = 2046 , t2.ranking >= 0 , t2.w_id in (22404, 22505) group t1.id result:
id q_id content w_id c_id ranking ------------------------------------------------------- 100 2046 a1 22404 100 0 101 2046 a2 22404 101 1 102 2046 a3 22404 102 1 103 2046 a4 22404 103 1 104 2046 a5 22404 104 2 105 2046 a6 22404 105 2 106 2046 a7 22505 a1 1 the c_id of row 106 should a7 instead of a1, , ranking wrong. have 20rows (a1-a20). rows of a7-a20 wrong. c_id , ranking of a7-a20 became a1 , 1.
in addition solution identified noel, it's worth mentioning 22505 records appear need joined on c_id matching table_1's content instead of id, have unnecessary join, , existing query mixes implicit , explicit joins - instead, try this:
select t1.id, t1.content, avg(t2.ranking+1) av, count(t2.w_id) total, count(if(ranking=0,1,null)) e, count(if(ranking=1,1,null)) g, count(if(ranking=2,1,null)) n, count(if(ranking=3,1,null)) b, count(if(ranking=4,1,null)) v, stddev(ranking) sd table_1 t1 join table_2 t2 on t2.c_id in (t1.id, t1.content) t1.q_id = 2046 , t2.q_id = 2046 , t2.ranking >= 0 , t2.w_id in (22404, 22505) group t1.id sqlfiddle here.
Comments
Post a Comment