Select operation

sami tarawneh
sami tarawneh used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
shoudn't you need a student_id in your table: payment?
Software Team Lead
Commented:
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

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
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

Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

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 Expert
Awarded 2016
Top Expert 2016

Commented:
Hey sami, did u try my suggestion ? It will give you the same output.
Ryan ChongSoftware Team Lead

Commented:
@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.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
@Ryan

No. It will give the same result as yours.
Ryan ChongSoftware Team Lead

Commented:
well, check this out, the sample i drafted out the solution...
29065405.accdb

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial