sql new

PHOTO EMBED

Fri Feb 18 2022 14:02:03 GMT+0000 (Coordinated Universal Time)

Saved by @vvnezapnopwnz #json

SELECT distinct ROUND(avg(b.points)) as average_points,
            b.test_date,
            b.theme, b.max_points,
            b.score_five, b.score_four, b.score_three,
            ROUND(AVG(ROUND(cast(b.points as decimal) / b.max_points * 100))) as percents,
            CASE 
            WHEN AVG(ROUND(cast(b.points as decimal) / b.max_points * 100)) > b.score_five THEN 5
            WHEN AVG(ROUND(cast(b.points as decimal) / b.max_points * 100)) > b.score_four 
            AND AVG(ROUND(cast(b.points as decimal) / b.max_points * 100)) < b.score_five THEN 4
            WHEN AVG(ROUND(cast(b.points as decimal) / b.max_points * 100)) > b.score_three
            AND AVG(ROUND(cast(b.points as decimal) / b.max_points * 100)) < b.score_four THEN 3
            ELSE null 
            END
            AS average_grade,
            SUM 
            ( CASE
            WHEN ROUND(cast(b.points as decimal) / b.max_points * 100) < b.score_three THEN 
              1
            ELSE
              0
            END
            ) AS bad_grade
            from custom_tests_results b
            join group_custom_tests a
            on b.custom_test_id = a.id
            where a.group_id = ${groupId}
            and a.format = '${format.format}' and b.subject_id = ${subject.subject_id}
            GROUP BY b.test_date, b.theme, b.max_points,
            b.score_five, b.score_four, b.score_three
content_copyCOPY