DROP VIEW IF EXISTS scoring_by_setting_view;
CREATE VIEW scoring_by_setting_view (
    user_id,
    scoring,
    scoring_weight,
    total_id_scoring,
    total_scoring
) as
WITH 
cte_scoring_weight_total as (
	SELECT sum(value) as total_weight FROM `config_scoring` where config_scoring.status = 1
    and config_scoring.key NOT IN ( select id FROM `config_scoring`  where config_scoring.key IN  (2,8))
)
SELECT scoring.user_id ,sum(scoring.scoring) as scoring, sum(scoring.current_config_scoring_value) as scoring_weight, 
count(scoring.id) as total_id_scoring, ( ( ( (sum(scoring.scoring)/ count(scoring.id) * 100 ) / cte_scoring_weight_total.total_weight) ) ) as total_scoring 
FROM scoring
INNER JOIN cte_scoring_weight_total on 1=1
where scoring.config_scoring_id is not null
GROUP by user_id;
