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

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

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

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

student_idin your table:payment?