sum of two different columns (with additions) of different tables and multiple table joining in Oracle

Question

I have three tables.

SCHOOL: schoolcode(PK), year, schoolname.
ENROLMENT: schoolcode, year, caste, c1, c2, c3, c4, c5, c6, c7, c8
CLASS: schoolcode, year, classid, rooms

Now, I want to find the list of schools with enrolment in class-1 to 4 and number of classrooms used by class 1-4 (CLASSID is defined as: 7 for class-1&2, 8 for class-3&4, 9 for class-5&6, 10 for class-7&8; and caste is defined as 1 for general, 2 for sc, 3 for st, 4 for others).

I used the following query:

select m.schoolcode, m.schoolname, sum(e.c1+e.c2+e.c3+e.c4), sum(c.rooms) 
from dise2k_enrolment09 e, 
     dise2k_master m ,
     dise2k_clsbycondition c 
where m.schoolcode=e.schoolcode and
      m.schoolcode=c.schoolcode and 
      e.year='2011-12' and 
      m.year='2011-12' and 
      c.year='2011-12' and 
      c.classid in(7,8) and 
      e.caste in(1,2,3,4) 
group by m.schoolcode, m.schoolname 

But the result showing is not correct. Enrolment is showing much higher than actual, same in case of classrooms.

Answer 1

Ok, try this to see if your problem arises from duplicating records in join:

select m.schoolcode, m.schoolname, e_sum, c_sum 
  from dise2k_master m
 inner join
 (
    select schoolcode,
           sum(c1 + c2 + c3 + c4) e_sum
      from dise2k_enrolment09
     where year='2011-12'
       and caste in(1,2,3,4) 
     group by schoolcode
 ) e
    on m.schoolcode=e.schoolcode
 inner join
 (
    select schoolcode,
           sum(rooms) c_sum
      from dise2k_clsbycondition
     where year='2011-12'
       and classid in(7,8)
     group by schoolcode
 ) c
    on m.schoolcode=c.schoolcode
 where m.year='2011-12'