Использование SUM() в предложении ON соединения
У меня есть 3 таблицы с именами «студенты», «студент_курсы» и «оценки». И для каждого студента мне нужно рассчитать общий средний балл. Так что я немного застрял. :(
студенческий стол
Student_courses
оценки
Вот мой запрос, который вызывает ошибку. (Код ошибки 1111. Недопустимое использование групповой функции) Я использую MySQL, но был бы рад получить стандартное решение SQL.
SELECT student_id, student_name,
(select g.GPA
from course_student AS cs
inner join Grades AS g ON (sum(cs.percent_grade) / count(*)) BETWEEN g.from_percent AND g.to_percent
where cs.student_id = students.id) As GPA
FROM students
Ожидаемым результатом будет список всех учащихся в таблице учащихся и их соответствующий общий средний балл.
Обновление 1.
Я только что заставил его работать на одного студента. Но мне нужно, чтобы это работало для всех учеников в таблице студентов. Вот код для одного студента.
select g.GPA, g.from_percent, g.to_percent
from course_student AS cs
inner join Grades AS g
where cs.student_id = 77
group by g.GPA, g.from_percent, g.to_percent
HAVING (sum(cs.percent_grade) / count(*)) BETWEEN g.from_percent AND g.to_percent
Пожалуйста, добавьте ожидаемый результат к вашему вопросу и объясните необходимую вам логику. Не пишите комментарии, вместо этого отредактируйте свой вопрос. Если вам нужно решение для разных учащихся, вам следует соответствующим образом добавить их в образец данных.
Данные таблицы примеров — это здорово, но минимально воспроизводимый пример также должен включать ожидаемый результат.
Обновил вопрос с таблицей ожидаемого результата. :) Спасибо
Ответы 1
Мы можем создать CTE StudentGrades, который группируется по студентам и рассчитывает среднюю оценку по всем их курсам. Затем мы можем использовать AvgGrade для объединения в таблице оценок.
WITH StudentGrades as
(
SELECT s.student_id, s.student_name, SUM(percent_grade) as TotalGrade, count(*) as CourseCount, CAST(SUM(percent_grade) / count(*) AS INT) as AvgGrade
FROM students s
INNER JOIN student_courses c on s.student_id=c.student_id
GROUP BY s.student_id, s.student_name
)
SELECT sg.student_id, sg.student_name, g.GPA
FROM StudentGrades sg
INNER JOIN grades g on g.from_percent <= sg.AvgGrade AND g.to_percent >= sg.AvgGrade
Великолепно. МОЙ уровень знаний SQL не простирается так далеко, поэтому я бы определенно не достиг этого самостоятельно. :) Отмечаем это как принятый ответ.
Зачем такое странное разделение? Мы можем просто использовать AVG(percent_grade). Поскольку вы не проверяете, равны ли значения нулю, вы рискуете получить исключение.
В дизайне есть несколько ошибок. Зачем вообще нужен столбец GPA или таблица оценок? Разве AvgGrade не будет более точным? Если бы AvgGrade был равен 69,9, оценка не была бы найдена, поскольку нет записи, соответствующей этому значению. Вот почему я преобразовал его в INT.
@Jonas Metzler Я придерживался стиля LostAndConfused и старался сохранить как можно больше от их оригинальной попытки. Ваше предложение — хороший шаг по оптимизации.
@BartMcEndree Это не просто «оптимизация», это избежание возможных исключений деления на ноль. Я бы также внес некоторые другие изменения, поэтому расширил вашу скрипку: dbfiddle.uk/F-wShEA5 Обратите внимание, что я намерен просто улучшить ваш ответ, а не сказать, что он плохой. В любом случае ваш запрос находится на правильном пути. Может быть, вы могли бы посмотреть и подумать, полезно это или нет для вас/ОП.
Вы оба очень полезны. Спасибо. @Барт, я обновлю таблицу оценок, включив в нее 69,99, которые ты поймал. Я публиковал данные непосредственно от своих пользователей, не проводя по ним технической проверки. РЖУ НЕ МОГУ
@Джонас, замечательное предложение. Прежде чем опубликовать его здесь, я несколько удалил SQL, что исключило возможность деления на ноль. Но тот факт, что это упрощает запрос, хорош, поскольку его легче читать и поддерживать.
Возможно, эта рабочий пример поможет нам совместно найти решение dbfiddle.uk/LqKCZTZl