每个RegistrationID中的问题相同的总和值 [英] problem same sum value in each RegistrationID

问题描述

select StudentFeesDetails.RegistrationID,StudentRegistration.StudentName,StudentRegistration.StudentFName,
StudentRegistration.DateOfAddmition,FeesMaster.TotalFees,
(select SUM(cast(StudentFeesDetails.LateFeeAmount +  StudentFeesDetails.PaidAmount as int))from StudentFeesDetails)as TotalWithLateFees,
(select SUM(cast(StudentFeesDetails.PaidAmount as int))from StudentFeesDetails)as TotalWithoutLateFees,
(select (cast(FeesMaster.TotalFees as int)) - sum(cast(StudentFeesDetails.PaidAmount as int))from StudentFeesDetails)as Balance,
(select SUM(cast(StudentFeesDetails.PaidAmount as int)) from StudentFeesDetails where StudentFeesDetails.FeeType='1') as RegistrationFees,
(select SUM(cast(StudentFeesDetails.PaidAmount as int)) from StudentFeesDetails where StudentFeesDetails.FeeType='2') as TutationFees,
(select SUM(cast(StudentFeesDetails.PaidAmount as int)) from StudentFeesDetails where StudentFeesDetails.FeeType='3') as ExamFees,
(select SUM(cast(StudentFeesDetails.LateFeeAmount as int)) from StudentFeesDetails where StudentFeesDetails.FeeType='1') as RLateFees,
(select SUM(cast(StudentFeesDetails.LateFeeAmount as int)) from StudentFeesDetails where StudentFeesDetails.FeeType='2') as TLateFees,
(select SUM(cast(StudentFeesDetails.LateFeeAmount as int)) from StudentFeesDetails where StudentFeesDetails.FeeType='3') as ELateFees
 from StudentFeesDetails inner join StudentRegistration  on StudentFeesDetails.RegistrationID=StudentRegistration.RegistartionID
 inner join SessionMaster on StudentFeesDetails.SessionID=SessionMaster.SessionID
 inner join SessionMonthMaster on SessionMonthMaster.SessionMonthID=StudentFeesDetails.SessionMonthID
 inner join BranchMaster on BranchMaster.BranchID=StudentFeesDetails.BranchID
 inner join CourseMaster on CourseMaster.CourseID=StudentFeesDetails.CourseID
 inner join SemesterMaster on SemesterMaster.SemesterID=StudentFeesDetails.SemesterID
 inner join FeesMaster on FeesMaster.FeesID=StudentFeesDetails.FeesID
 where StudentFeesDetails.CourseID= 'C-001'and StudentFeesDetails.BranchID='B-001'and StudentFeesDetails.SessionID='101' and StudentFeesDetails.SemesterID='S-001'and StudentFeesDetails.SessionMonthID='1001' and StudentFeesDetails.Flag !='D'
 group by StudentFeesDetails.RegistrationID,StudentRegistration.StudentName,
 StudentRegistration.StudentFName,StudentRegistration.DateOfAddmition,FeesMaster.FeesID,FeesMaster.TotalFees

推荐答案

我怀疑你要做的事情比这简单得多： GROUP BY 子句中的项目数量尖叫我猜是因为它不会让我使用这些字段除非我将它们全部列出



GROUP BY 为每个列创建一个新组条款：所以，如果你有一个基本的表格：

I suspect that what you are trying to do is a lot simpler than that: the number of items in the GROUP BY clause screams "I'm guessing because it won't let me use these fields unless I list them all"

GROUP BY creates a new group for each column in the clause: so if you have a basic table:

A  B  C
1  1  1
1  1  2
1  2  3
1  2  4
2  1  1
2  1  2
2  2  3
2  2  4

然后 GROUP BY A 将创建两个组（A == 1和A == 2）

但 GROUP BY A，B 将创建4组（A == 1和B == 1，A == 1和B == 2，A == 2和B == 1，A == 2和B == 2）

所以你的 GROUP BY 子句可能会产生近乎一个很多团体，因为你的桌子上有行！



我建议你坐下来看看你的数据，然后考虑你想要的输出得到 - 然后尝试用简单的块来构建它。因为只是在那里潇洒并希望它能够发挥作用是不会的！ ：笑：

这看起来很像你一直试图做的。

Then GROUP BY A will create two groups (A == 1 and A == 2)
But GROUP BY A, B will create 4 groups (A == 1 and B == 1, A == 1 and B == 2, A == 2 and B == 1, A == 2 and B == 2)
So your GROUP BY clause will probably generate nearly as many groups as there are rows in your table!

I'd suggest that you sit down, look at your data, and think about what output you are trying to get - and then try to build it up in easy chunks. Because just dashing in there and hoping it will work isn't going to! :laugh:
And that looks a lot like what you have been trying to do.

这篇关于每个RegistrationID中的问题相同的总和值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！