0

I have three tables.

  1. table SCHOOL: schoolcode(PK), year, schoolname.
  2. table ENROLMENT: schoolcode, year, classname, enrol
  3. table CLASS: schoolcode, year, classid, rooms

Now, I want to find the list of schools with enrollment in classname - 1 to 4 and number of classrooms used by class 1-4.

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 
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 classid in (1,2,3,4) 
    and e.classname in (1,2,3,4) 
group by 
    m.schoolcode, m.schoolname

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

Improve this question
0

2 Answers 2

Reset to default
1

Try this:

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(1,2,3,4) 
and e.classname = c.classid
group by m.schoolcode, m.schoolname

The way you have it: and e.classname in(1,2,3,4) is like having an OR operator in your where clause.

(c.classid=1 or c.classid=2 or c.classid=3 or c.classid=4) 
and 
(e.classname=1 or e.classname=2 or e.classname=3 or e.classname=4)

So, c.classid can be "1" and e.classname can be "2" which is wrong

UPDATE I still think the problem is that you don't connect the c.classid with e.classname

Try it like this:

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(1,2,3,4) 
and c.classid = decode(e.classname,1,7,2,7,3,8,4,8,5,9,6,9,7,10,8,10)
group by m.schoolcode, m.schoolname
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

No. Classname and classid are not the same thing. its an example. sorry, its my mistake, that i couldnt explain my problemto you very well. actually, classname has values 1 to 8, but classid has values 7,8,9,10 (as 7 for class 1,2 ; 8 for class 3,4; 9 for class 5,6; and 10 for class 7,8). I wrote, classid in(7,8). Now can you help me to find the answer. because both the above answers didnt work.
0

try this:

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

2 Comments

No. Classname and classid are not the same thing. its an example. sorry, its my mistake, that i couldnt explain my problemto you very well. actually, classname has values 1 to 8, but classid has values 7,8,9,10 (as 7 for class 1,2 ; 8 for class 3,4; 9 for class 5,6; and 10 for class 7,8). now can you help me to find the answer. because both the above answers didnt work.
No. Classname and classid are not the same thing. its an example. sorry, its my mistake, that i couldnt explain my problemto you very well. actually, classname has values 1 to 8, but classid has values 7,8,9,10 (as 7 for class 1,2 ; 8 for class 3,4; 9 for class 5,6; and 10 for class 7,8). I wrote, classid in(7,8). Now can you help me to find the answer. because both the above answers didnt work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.