mysql - Is there a way to count occurrence of values of multiple column in SQL? -

let have table storing survey result, , syntax looks this:

id | q1 | ..... | q30 | created_at 

created_at timestamp column , others integers fields.

now want have result of survey according month. 1 question, have:

select year(created_at) year, month(created_at) month, q1, count(*) occurrence   survey_table  group year(created_at), month(created_at), q1 

the return like:

year | month| q1 | occurence 2016 | 11   | 1  | 10 2016 | 11   | 2  | 15 2016 | 11   | 3  | 2 2016 | 10   | 1  | 12 2016 | 10   | 2  | 2 2016 | 10   | 3  | 50 

the data passed php script further calculation , data-display.

to calculation on 30 columns, 1 way perform query 30 times different question. wondering if there way in single query output this:

year | month| q1_1 | q1_2 | q1_3 | q2_1 | q2_2 | q2_3 | ... | q30_1 | q30_2 | q30_3 2016 | 11   | 10   | 15   | 2    | 2    | 20   | 5    | ... | 5     | 15    | 7     2016 | 10   | 12   | 2    | 50   | 25   | 27   | 12   | ... | 20    | 24    | 20 

is there way in 1 query? if yes, performance better?

this how query look:

select    year(created_at) year,    month(created_at) month,    count(q1 = 1) q1_1,   count(q1 = 1) q1_2,   count(q1 = 1) q1_3,   count(q1 = 2) q2_1,   ...   count(q30 = 3) q30_3 survey_table group year(created_at), month(created_at); 

it seems, however, better change table design:

 q_type | q_value |created_at -------+---------+---------- 1      | 1       | 2016-10-05 2      | 3       | 2016-10-05 3      | 1       | 2016-10-05 4      | 2       | 2016-10-05 ... 30     | 1       | 2016-10-05 ... 29     | 1       | 2016-10-08 30     | 2       | 2016-10-08 

and query be:

select    year(created_at) year,    month(created_at) month,    q_type,   q_value,   count(*) survey_table group year(created_at), month(created_at), q_type, q_value; 

you'd formatting, i.e. putting data in grid, in php. more flexible, query doesn't have know longer how many q types , how many q values exist.