Link to home
Create AccountLog in
Avatar of sami tarawneh
sami tarawneh

asked on

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
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

shoudn't you need a student_id in your table: payment?
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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

Avatar of sami tarawneh
sami tarawneh

ASKER

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
Hey sami, did u try my suggestion ? It will give you the same output.
@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.
@Ryan

No. It will give the same result as yours.
well, check this out, the sample i drafted out the solution...
29065405.accdb