select device_id,university,
sum(case when question_id is not null then 1 else 0 end) as question_cnt,
sum(case when result = "right" then 1 else 0 end) as right_question_cnt
from (
	select u.device_id,university,question_id,result
	from user_profile u
	left join question_practice_detail q on u.device_id = q.device_id
	where university = "复旦大学" and (month(date) = 8 or date is null)
) t1
group by device_id,university;