用总和组计算总平均成绩 [英] Calculate the general average grade with sum group by

问题描述

我必须为每个用户显示名字和姓氏，学习年份，年龄，平均成绩，以及一般平均成绩.

I have to display for each user the first and last name, year as studyYear, age, average grade, and also the general average grade.

我的一般平均成绩需要这样计算:

General average grade I need to be computed like this:

总和(AverageGrade)/成绩总数

[userID]
[FirstName]
[LastName] 
[BirthDate] 
[Year] 
[AverageGrade] 

我的问题是一般平均成绩的总和.我了解我必须使用 Sum(AverageGrade) + group by ，但这行不通，有帮助吗?

My problem is the sum of the the general average grade. I understand that I have to use Sum(AverageGrade) + group by but it wont work, help?

select FirstName+' '+LastName as FullName,
AverageGrade ,Year as StudyYear,
DATEDIFF(year,BirthDate,GETDATE())  AS AgeInYears
from Student

如何在此处包含 Sum(AverageGrade)?还是我该如何重写?

How do I include the Sum(AverageGrade) here? Or how do I rewrite?

  FirstName LastName   Year   AverageGrade  [GeneralAverageGrade]
 
  Fatima       Alo      2         9.20         7.45
  Omar       Kutum      1         5.88         7.45
  Sado      Kerkzm      3         7.20         7.45

推荐答案

您是否只希望对整个数据集的 averagegrade 列取平均值?如果是这样，请使用窗口功能:

Do you just want a grand average of column averagegrade over the whole dataset? If so, use window functions:

select s.*, avg(averagegrade) over() avg_averagegrade
from students s

这篇关于用总和组计算总平均成绩的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！