如何查找按别名总和列值求和？

Question

在这里，我试图计算特定学生支付的学费的SUM，特别是该财年以及该财年每个月的学费。

例如，在下面的行中，PaidTutionFee是由SUM(Tution) AS PaidTutionFee计算的。现在我想要的是对PaidTutionFee列求和，

StudentId   FYID    Month   PaidTutionFee
7           16      3       2855.00
7           16      4       2855.00

为此，我将学费相加，即SUM(Tution) AS PaidTutionFee和group by StudentId, FYId, Month，这将给出该财年每个月的学费总和，最后是整个财年支付的全部学费，我将SUM(Tution) AS PaidTutionFee的别名相加，即SUM(PaidTutionFee) AS TotalTutionFeeInFiscalYear。

现在的问题是我抛出了一个错误

Msg 207, Level 16, State 1, Line 11
Invalid column name 'PaidTutionFee'.

下面是我的sql server查询

SELECT StudentId,FYId, Month, SUM(Tution) AS PaidTutionFee, 
SUM(PaidTutionFee) AS TotalTutionFeeInFiscalYear
FROM [dbo].[FeePaymentDetails]
WHERE FYId = 16 AND StudentId = 7 
GROUP BY StudentId, FYId, Month

Answer 1

不能在同一个SELECT语句中聚合聚合。但是..。这并不是你真正想要的。当按StudentID, FYid, Month分组时，您想要的和是tuition，而当按StudentID, FYid分组时，您想要的和是tuition (如果我没看错的话)。为此，您可以使用窗口函数：

SELECT StudentId,FYId, Month, SUM(Tution) AS PaidTutionFee
Sum(Tution) OVER (PARTITION BY SutdentID, FyID) as TotalTutionFeeInFiscalYear
FROM [dbo].[FeePaymentDetails]
WHERE FYId = 16 AND StudentId = 7 
GROUP BY StudentId, FYId, Month;

现在您的结果集的粒度是在StudentId, FYId, Month级别，但是您有一个额外的列，它的值对每个不同的StudentId, FYId重复，它的值表示您想要的更高粒度级别的总和。

Answer 2

sqlserver的rollup和grouping sets功能很容易做到这一点：

SELECT StudentId,FYId, Month, SUM(Tution) AS PaidTutionFee
Sum(Tution) OVER (PARTITION BY SutdentID, FyID) as TotalTutionFeeInFiscalYear
FROM [dbo].[FeePaymentDetails]
/*WHERE FYId = 16 AND StudentId = 7*/ 
GROUP BY ROLLUP(StudentId, FYId, Month)

我选择了ROLLUP，因为我认为它最容易理解

Sqlserver将按照您的要求进行分组，直到月份级别，因此您将获得每个月的一行。然后，多亏了ROLLUP的添加，您将得到一个空月份的行，这是学生/年份的所有内容的总和。您还将得到一行null month和null year，这是该学生的总和，还有一行所有null，这是所有学生的总计。因此，效果是分组/求和按照group by中列的顺序从最精确(学生/年/月)到最不精确(所有内容的总和)从右到左“滚动”

我注释掉了你的where子句，以便更好地演示汇总效果-如果你把它放回去，你会得到一些与你已经知道的没有什么不同的合计行(因为只有一个学生和一年，所以所有学生的合计将与一个学生的合计相同，等等)

使用分组集可以更好地微调小计/总计效果

如果你是这样创建你的群的：

GROUP BY GROUPING SETS((StudentId,FyId,Month),(StudentId,FyId))

您将只获得月份的详细信息(感谢第一个分组集)，以及由于第二个分组集，month为空的行(年份的总数)

Answer 3

您可以使用over Partition By计算运行总计和总计，而不需要group by：

SELECT StudentId,FYId, Month, SUM(Tuition) over (partition by StudentId, FyId) AS PaidTutionFee, 
SUM(Tution) over (partition by StudentId, Year(FyId)) AS TotalTutionFeeInFiscalYear
FROM [dbo].[FeePaymentDetails]
WHERE FYId = 16 AND StudentId = 7 
Order BY StudentId, FYId, Month