CREATE VIEW scoring_rate_view (
 user_id,
 participant_type,
 scoring_type,
 rate
) as
SELECT 
    scoring_amount.user_id                                      AS user_id,
    scoring_amount.participant_type                             AS participant_type,
    scoring_amount.scoring_type                                 AS scoring_type,
    scoring_amount.total_scoring/scoring_count.count_scoring    AS rate
FROM (
SELECT user_id, participant_type, scoring_type, sum(scoring) total_scoring
FROM `scoring`
GROUP BY user_id, participant_type, scoring_type
) scoring_amount
INNER JOIN (
SELECT user_id, participant_type, scoring_type, count(scoring) count_scoring
FROM `scoring`
WHERE scoring > 0
GROUP BY user_id, participant_type, scoring_type
) scoring_count on scoring_amount.user_id = scoring_count.user_id and scoring_amount.participant_type = scoring_count.participant_type and
scoring_amount.scoring_type = scoring_count.scoring_type