select u.device_id,university, count(question_id) question_cnt
,sum(case when result='right' then 1 else 0 end ) right_question_cnt
from user_profile u
left join question_practice_detail q
on u.device_id = q.device_id and DATE_FORMAT(date,'%m')='08'
where university = '复旦大学'
group by university ,u.device_id
,sum(case when result='right' then 1 else 0 end ) right_question_cnt
from user_profile u
left join question_practice_detail q
on u.device_id = q.device_id and DATE_FORMAT(date,'%m')='08'
where university = '复旦大学'
group by university ,u.device_id