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.

Possible duplicate stackoverflow.com/questions/11828046/… And even 39min ago asked again and removed by author stackoverflow.com/questions/11843311/… — Yaroslav
– Yaroslav, Commented Aug 7, 2012 at 10:31
Do you really have columns named c1,c2,..? That sounds like a non-normalized design. — user330315
– user330315, Commented Aug 7, 2012 at 10:46

Nikola Markovinović · Accepted Answer · 2012-08-07 10:41:23Z

6

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'

answered Aug 7, 2012 at 10:41

Nikola Markovinović

19.4k5 gold badges48 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related