select qs_stat.surveyls_title, qs_stat.language, qs_stat.num_qs, qs_stat.percent_trans as pct_q_trans, as_stat.percent_trans as pct_a_trans
from
(select l.surveyls_title , a.language, a.num_qs, a.percent_trans
from
(select b.sid, b.language, count(b.qid) as num_qs, (sum(b.translated) / count(b.translated)) * 100 as percent_trans
from
(select q.sid, q.qid, q.question, q.language, 
	(case when q.question != e.question then 1 else 0 end) as translated
from limesurvey_questions as q, limesurvey_questions as e
where q.type != '*' and q.language != 'en' and
	e.sid = q.sid and e.qid = q.qid and e.language = 'en') as b
group by b.sid, b.language) as a,
limesurvey_surveys_languagesettings l
where a.sid = l.surveyls_survey_id and l.surveyls_language = 'en'
order by a.sid, a.percent_trans desc, a.language) as qs_stat,
(select l.surveyls_title, qstat.sid, qstat.language, qstat.num_qs, qstat.percent_trans
from
(select ansstat.sid, ansstat.language, count(distinct ansstat.qid) as num_qs, (sum(ansstat.pct_trans) / count(ansstat.pct_trans)) * 100 as percent_trans
from
(
select q.sid, q.qid, q.language, (sum(trans.translated)/ count(trans.translated)) as pct_trans
from
(select ans.qid, ans.answer, ans.language, 
	(case when ans.answer != en.answer then 1 else 0 end) as translated
from limesurvey_answers as ans, limesurvey_answers as en
where ans.language != 'en' and en.language = 'en' and
	ans.qid = en.qid and ans.scale_id = en.scale_id and ans.code = en.code) as trans,
limesurvey_questions as q
where q.qid = trans.qid and q.language = trans.language
group by q.qid, q.language
) as ansstat
group by ansstat.sid, ansstat.language) 
as qstat,
limesurvey_surveys_languagesettings l
where qstat.sid = l.surveyls_survey_id and l.surveyls_language = 'en'
order by qstat.sid, qstat.percent_trans desc, qstat.language) as as_stat
where qs_stat.surveyls_title = as_stat.surveyls_title and qs_stat.language = as_stat.language
order by qs_stat.surveyls_title, qs_stat.percent_trans desc, qs_stat.language