select device_id,university,count(question_id) as question_cnt,sum(new_result) as right_question_cnt
from
(select a.device_id,university,question_id,
case when result='right' then 1 else 0 end new_result,
`date`
from user_profile a
left join question_practice_detail b 
on a.device_id = b.device_id and month(b.date) = 8
where university = '复旦大学' ) as t
group by device_id,university