Select operation

I have two tables as follows:

i need to a select statement to show the following for each class
class,sum of total,sum of remaining,sum of ammount
thanks to all
sami tarawnehAsked:
Who is Participating?
Ryan ChongCommented:
or you can try something like this?

select a.class, a.SumOftotal, a.SumOfremaining, b.SumOfammount from

SELECT Students.class, Sum( AS SumOftotal, Sum(Students.remaining) AS SumOfremaining
FROM Students
GROUP BY Students.class
) as a 

inner join

SELECT payments.class, Sum(payments.ammount) AS SumOfammount
FROM payments
GROUP BY payments.class
) as b on a.class = b.class

Open in new window

Ryan ChongCommented:
shoudn't you need a student_id in your table: payment?
Pawan KumarDatabase ExpertCommented:
Please try this-

SELECT p.class , SUM( as total , SUM(s.remaining) as remaining , SUM(p.ammount) as ammount
Students as s
INNER JOIN payments as p ON p.class = s.class
GROUP BY p.class

Open in new window

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sami tarawnehAuthor Commented:
thanks alot
thats what i was looking for, except for replacing inner join with left join to get the classes with no payments

thanks again
Pawan KumarDatabase ExpertCommented:
Hey sami, did u try my suggestion ? It will give you the same output.
Ryan ChongCommented:

I think based on your statement above, it may return different (multiply effect) results if there are multiple payments based on the joining conditions.
Pawan KumarDatabase ExpertCommented:

No. It will give the same result as yours.
Ryan ChongCommented:
well, check this out, the sample i drafted out the solution...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.