Subversion Repositories VORC

## View: v_survey_results

#

# +----------+------------------+------+-----+---------+-------+

# | Field    | Type             | Null | Key | Default | Extra |

# +----------+------------------+------+-----+---------+-------+

# | classid  | int(11) unsigned | YES  |     | NULL    |       |

# | qid      | int(11) unsigned | NO   |     | 0       |       |

# | qorder   | int(11) unsigned | NO   |     | 0       |       |

# | private  | tinyint(4)       | NO   |     | 0       |       |

# | type     | varchar(45)      | NO   |     |         |       |

# | question | varchar(255)     | NO   |     |         |       |

# | average  | double           | YES  |     | NULL    |       |

# | count    | bigint(21)       | NO   |     | 0       |       |

# +----------+------------------+------+-----+---------+-------+

CREATE OR REPLACE VIEW `v_survey_results` AS select `v_survey_answer`.`classid` AS `classid`,`survey_question`.`qid` AS `qid`,`survey_question`.`qorder` AS `qorder`,`survey_question`.`private` AS `private`,`survey_question`.`type` AS `type`,`survey_question`.`question` AS `question`,round(avg(`v_survey_answer`.`response`),1) AS `average`,count(`v_survey_answer`.`response`) AS `count` from (`survey_question` join `v_survey_answer` on((`survey_question`.`qid` = `v_survey_answer`.`qid`))) where ((`survey_question`.`type` = 'range') and (`v_survey_answer`.`response` <> 0)) group by `v_survey_answer`.`classid`,`survey_question`.`qid` union select `v_survey_answer`.`classid` AS `classid`,`survey_question`.`qid` AS `qid`,`survey_question`.`qorder` AS `qorder`,`survey_question`.`private` AS `private`,`survey_question`.`type` AS `type`,`survey_question`.`question` AS `question`,count(nullif(trim(`v_survey_answer`.`response`),'')) AS `average`,count(`v_survey_answer`.`response`) AS `count` from (`survey_question` left join `v_survey_answer` on((`survey_question`.`qid` = `v_survey_answer`.`qid`))) where (`survey_question`.`type` <> 'range') group by `v_survey_answer`.`classid`,`survey_question`.`qid` order by `classid`,`qorder`