sql - 按 T​​ermID 和 yearID 的 SQL 平均值

Question

这是我的 SQL 代码包含表：tblstudentdetail 和 tblstudentinfo

 SELECT distinct  TblstudentInfo.FirstName +''+ TblstudentInfo.LastName as Name,TblstudentInfo.StudentID, TblstudentInfo.YearID, TblstudentInfo.DualCredits, TblstudentInfo.DC_Classes, TblstudentInfo.DC_GPA, tblStudentDetail.StatusID, TblstudentInfo.ActScore,tblStudentDetail.YearID
      ,tblStudentDetail.TermID,tblStudentDetail.GPA
FROM TblstudentInfo

left join tblStudentDetail on TblstudentInfo.StudentID = tblStudentDetail.StudentID
order by  name

结果：

Name StudentID  YearID  DualCredits DC_Classes  DC_GPA  StatusID    ActScore    YearID  TermID   GPA
Aaron   124             9   NULL        NULL     NULL     1            17        10     1      2.901
Aaron   124             9   NULL        NULL     NULL     1            17        10     2      3.171
Aaron   124             9   NULL        NULL     NULL     1            17        11     1      2.31
Aaron   124             9   NULL        NULL     NULL     1            17        11     2      3.31
Debbie  2344            7   NULL        NULL     NULL     1            19        9      1      4.31
Debbie  2344            7   NULL        NULL     NULL     1            19        10     1      4.31

我如何平均平均 GPA？我想要这样的结果：

Aaron   124             9   NULL        NULL     NULL     1            17      2.923
Debbie  2344            7   NULL        NULL     NULL     1            19       4.31

标签： sqlsql-server

Answer 1

只需在 GPA 上使用 AVG() 函数，然后按其余列分组

SELECT si.FirstName +''+ si.LastName as Name, si.StudentID,
        si.YearID, si.DualCredits, si.DC_Classes, 
        si.DC_GPA, si.StatusID, si.ActScore, si.YearID,AVG(d.GPA)
FROM TblstudentInfo si LEFT JOIN tblStudentDetail d
ON si.StudentID = d.StudentID
GROUP BY si.FirstName, si.LastName, si.StudentID,si.YearID, si.DualCredits, si.DC_Classes, si.DC_GPA, si.StatusID, si.ActScore, si.YearID;