php - Frequency list of each column in a table -
i have table looks this:
r01 r02 r03 r04 1 2 x x 1 2 x 1 x 1 2 1 x 2 2 2 1 2 1 x 1 1 1 2 1 x 1 1 1 2 x 1 2 2 x 2 and frequency array list each column (not row), similar array_count_values(). like
r01: 1 => 6, x => 2, 2 => 1 r02: 1 => 2, x => 1, 2 => 6 r03: 1 => 3, x => 4, 2 => 2 r04: 1 => 4, x => 2, 2 => 3 is possible to 1 or few mysql questions? have not come , idea. solution has data php , have data array , add 1 corresponding counter each row.
i can have 100->20000 rows. have mysql solution scales better php solution.
-- edit
i displaying simplified table structure think need show full table.
create table if not exists `tips_rows` ( `row_id` int(11) not null, `r01` enum('1','x','2') not null, `r02` enum('1','x','2') not null, `r03` enum('1','x','2') not null, `r04` enum('1','x','2') not null, `r05` enum('1','x','2') not null, `r06` enum('1','x','2') not null, `r07` enum('1','x','2') not null, `r08` enum('1','x','2') not null, `r09` enum('1','x','2') not null, `r10` enum('1','x','2') not null, `r11` enum('1','x','2') not null, `r12` enum('1','x','2') not null, `r13` enum('1','x','2') not null, primary key (`row_id`) ) engine=innodb default charset=utf8; and tips_rows contains combinations of values. (1,6 million rows) , have link table connection user tips_rows, ´tips_rows_users´
so link table hold number of users data set of 100-10000 row_id each user.
i have found 1 solution base on other stackoverflow googling. maybe not purest query works , fast.
select sum(case when r01 = '1' 1 else 0 end) r11, sum(case when r01 = 'x' 1 else 0 end) r1x, sum(case when r01 = '2' 1 else 0 end) r12, sum(case when r02 = '1' 1 else 0 end) r21, sum(case when r02 = 'x' 1 else 0 end) r2x, sum(case when r02 = '2' 1 else 0 end) r22, sum(case when r03 = '1' 1 else 0 end) r31, sum(case when r03 = 'x' 1 else 0 end) r3x, sum(case when r03 = '2' 1 else 0 end) r32, sum(case when r04 = '1' 1 else 0 end) r41, sum(case when r04 = 'x' 1 else 0 end) r4x, sum(case when r04 = '2' 1 else 0 end) r42, sum(case when r05 = '1' 1 else 0 end) r51, sum(case when r05 = 'x' 1 else 0 end) r5x, sum(case when r05 = '2' 1 else 0 end) r52, sum(case when r06 = '1' 1 else 0 end) r61, sum(case when r06 = 'x' 1 else 0 end) r6x, sum(case when r06 = '2' 1 else 0 end) r62, sum(case when r07 = '1' 1 else 0 end) r71, sum(case when r07 = 'x' 1 else 0 end) r7x, sum(case when r07 = '2' 1 else 0 end) r72, sum(case when r08 = '1' 1 else 0 end) r81, sum(case when r08 = 'x' 1 else 0 end) r8x, sum(case when r08 = '2' 1 else 0 end) r82, sum(case when r09 = '1' 1 else 0 end) r91, sum(case when r09 = 'x' 1 else 0 end) r9x, sum(case when r09 = '2' 1 else 0 end) r92, sum(case when r10 = '1' 1 else 0 end) r101, sum(case when r10 = 'x' 1 else 0 end) r10x, sum(case when r10 = '2' 1 else 0 end) r102, sum(case when r11 = '1' 1 else 0 end) r111, sum(case when r11 = 'x' 1 else 0 end) r11x, sum(case when r11 = '2' 1 else 0 end) r112, sum(case when r12 = '1' 1 else 0 end) r121, sum(case when r12 = 'x' 1 else 0 end) r12x, sum(case when r12 = '2' 1 else 0 end) r122, sum(case when r13 = '1' 1 else 0 end) r131, sum(case when r13 = 'x' 1 else 0 end) r13x, sum(case when r13 = '2' 1 else 0 end) r132 `tips_rows` r inner join tips_rows_users using (row_id) user__id='{userid}' this give me 1 result row as
r11 r1x r12 r21 r2x r22 r31 r3x r32 r41 r4x r42 r51 r5x r52 r61 r6x r62 r71 r7x r72 r81 r8x r82 r91 r9x r92 r101 r10x r102 r111 r11x r112 r121 r12x r122 r131 r13x r132 40 34 26 48 30 22 69 14 17 70 16 14 15 17 68 28 31 41 80 20 0 49 29 22 38 30 32 69 16 15 29 28 43 19 31 50 13 25 62 and can use in php-template file.
try this::
select 'r01', r01, count(1) mytable group r01 union select 'r02', r02, count(1) mytable group r02 union select 'r03', r03, count(1) mytable group r03 union select 'r04', r04, count(1) mytable group r04
Comments
Post a Comment