Click here to Skip to main content
16,012,611 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
please i want help to sum amount of fees for students and compare it with assigned one,
i know there is smg missing but how to select only SUM(FsPaid.Amount) AS totalfees and compare it with Fs1.amount
SQL
SELECT
                     SUM(FsPaid.Amount) AS totalfees
       FROM         FsPrepaidFees FsPre INNER JOIN
                     Students S ON FsPre.STCode = S.STCode INNER JOIN
                     FsFeesClass Fs ON Fs.FNCLCode = FsPre.FNCLCode AND Fs.ClassFeesCode = FsPre.ClassFeesCode INNER JOIN
                     FsFees  FsType ON FsType Fs.FNCLCode = FsType.FNCLCode AND Fs.feesCode = FsType.feesCode INNER JOIN
                     FsPaidFees FsPaid ON FsPre.FNCLCode = FsPaid.FNCLCode AND FsPre.prepaidCode = FsPaid.prepaidFeesCode INNER JOIN
                     Schools ON S.SCCode = Schools.SCCode AND S.STShift = Schools.SCShift INNER JOIN
                     EducationLevels INNER JOIN
                     Classes ON EducationLevels.EDCatCode = Classes.EDCatCode AND EducationLevels.EDLVLCode = Classes.EDLVLCode INNER JOIN
                     EducationCategories ON EducationLevels.EDCatCode = EducationCategories.EDCatCode ON S.CLSCode = Classes.CLSCode
       WHERE
         (dbo.S.sccode = 3)
         AND   (FsType.FNCLCode = 2) AND (FsType.feesType = 0) AND (FsPaid.flgPaid =  1) AND (FsPre.SCYear = 7) and
         (select Fs1.amount  from FsFeesClass Fs1
            group by Fs1.amount
            having Fs1.amount = SUM(FsPaid.Amount))
Posted
Updated 2-Mar-13 22:11pm
v2
Comments
Sandeep Mewara 3-Mar-13 4:14am    
Please elaborate more on what exactly are you trying to do/achieve?
amr_amrovitsh 3-Mar-13 4:26am    
i have fixed fees for grade say 2000 and this refers to fsfeesclass table
and i have students paid installments
i want to pick only students that paid all 2000 not just intallment
so what i want to do , i want to sum all fees for students that paid full assigned fee

1 solution

Hi,

I cant get exactly what you want but there is join problem with this line


SQL
INNER JOIN
                      EducationLevels INNER JOIN
                      Classes ON EducationLevels.EDCatCode = Classes.EDCatCode
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900