asked on
Select operation
I have two tables as follows:
Students
id,name,class,total,remain
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
Students
id,name,class,total,remain
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
Microsoft AccessSQL
Last Comment
Ryan Chong
shoudn't you need a student_id in your table: payment?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
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
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.
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.
No. It will give the same result as yours.
well, check this out, the sample i drafted out the solution...
29065405.accdb
29065405.accdb