Select operation

I have two tables as follows:
Students
                 id,name,class,total,remaining
payments
                id,st_name,ammount,class

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(Students.total) 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

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

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

Open in new window

0
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
1
 
Pawan KumarDatabase ExpertCommented:
Hey sami, did u try my suggestion ? It will give you the same output.
0
 
Ryan ChongCommented:
@Pawan,

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

No. It will give the same result as yours.
0
 
Ryan ChongCommented:
well, check this out, the sample i drafted out the solution...
29065405.accdb
0
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.