problem same sum value in each RegistrationID

Question

1.00/5 (2 votes)

See more:

SQL-Server-2008R2

SQL

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

Posted 16-Jun-15 20:56pm

Ashish Dey

Add a Solution

Comments

Abhipal Singh 17-Jun-15 3:10am

Please explain the problem.
Also, try to illustrate with sample data with what is expected and what is actully happening

Ashish Dey 17-Jun-15 4:45am

all registration id same sum value like
RegistrationID TotalWithLateFees TotalWithoutLateFees Balance RegistrationFees
R-0001 5000 3000 2000 1000
R-0002 5000 2000 2000 1000
all paid amount is different but only sum in First RegistrationID
how to calculate sum in sessionwise, corusewise,..... for every student

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2015-06-16T21:43:00

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:

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.