Group function

[Kunal Lakhani]

I have a table named tuitionpayment with columns named enrollmentno,fullname, payment,due,discount (no primary key). I need to get max(due),sum(payment), and sum (discount) for every enrollment no.

This gets me the details of a particular enrollment no :

Select enrollmentno,fullname,(SELECT sum(payment) from tuitionpayment where enrollmentno=1004) "Total Payment",(SELECT max(due) from tuitionpayment where enrollmentno=1004) "Total Fees",(SELECT sum(discount) from tuitionpayment where enrollmentno=1004) "Total Discount"  from tuitionpayment where enrollmentno=1004

OUTPUT

ENROLLMENTNO	 FULLNAME	Total Payment	Total Fees	Total Discount
1004	            A	     3500            20000	        5000
1004	            A        3500	         20000	        5000
1004	            A        3500	         20000	        5000

The above query gets me the detail of enrollmentno 1004, but, its gets me 3 rows with same date.
I need to get max(due),sum(payment), and sum (discount) for every enrollment no.something like this :

ENROLLMENTNO	FULLNAME	Total Payment	Total Fees	Total Discount
1004	            A	     3500            20000	        5000
1006	            B        7000	         15000	        0

[Paul Clapham]

Well, that seems like kind of an awkward way to do it, for a start, even if it worked. Wouldn't it be easier to just do a single Group-By query?
Select enrollmentno, fullname, sum(payment), max(due), sum(discount) from tuitionpayment where enrollmentno=1004 Group By enrollmentno, fullname

[Kunal Lakhani]

Thanks Paul Clapham for your reply.

You are right. My way was very awkward. I didn't thought of group by. Thanks once again Paul