New query

[kaz]

Hi everyone,

have yet another query which is proving a bit challenging for my novice status
- any help would be appreciated, I am using Oracle:

Table structure is:

Unit Enrolment Student
---- --------- -------
unit_code unit_code stu_no
name stu_no name
room_no grade degree

The query is:

For each room, list all the students who have had at least 4 classes in that
room.
I used: select u.room_no, s.stu_no
from u,e,s
where (u.unit_code=e.unit_code) and
(e.stu_no=s.stu_no)
order by u.room_no, s.stu_no

and this gave me a list of the rooms and the student enrolments corresponding,
but I somehow need to count the times each distinct stu_no appears for each
room_no so I can display those which are >=4. Its this bit I am having trouble
with.

Any ideas???

Thanks, Karen

[Paul Mc]

G'day Kaz.

Try something like:

select u.room_no, e.stu_no
from Unit u, Enrolment e
where (u.unit_code=e.unit_code)
Group By u.room_no, e.stu_no
having Count(e.stu_no) >= 4
order by u.room_no, e.stu_no

Basically this query (In the Group By ... Having clause) counts the number
of times each stu_no appears next to each disting room_no, and filters the
results where that count is >= 4.

The syntax I have used is fairly standard, Oracle should handle that no worries.


HTH
Cheers,
Paul

>
>Hi everyone,
>
>have yet another query which is proving a bit challenging for my novice
status
>- any help would be appreciated, I am using Oracle:
>
>Table structure is:
>
>Unit Enrolment Student
>---- --------- -------
>unit_code unit_code stu_no
>name stu_no name
>room_no grade degree
>
>The query is:
>
>For each room, list all the students who have had at least 4 classes in
that
>room.
>I used: select u.room_no, s.stu_no
> from u,e,s
> where (u.unit_code=e.unit_code) and
> (e.stu_no=s.stu_no)
> order by u.room_no, s.stu_no
>
>and this gave me a list of the rooms and the student enrolments corresponding,
>but I somehow need to count the times each distinct stu_no appears for each
>room_no so I can display those which are >=4. Its this bit I am having
trouble
>with.
>
>Any ideas???
>
>Thanks, Karen
>
>
>

[kaz]

You're a legend, Paul!

Thanks for the help, I must say I was a little surprised to see the 'G'day
Kaz' at the top of the message, as I am an aussie, but living in London.
Anyway, really appreciate the assistance, this off-campus study can be a
little hard at times when you don't have any assistance.

Cheers,
kaz


"Paul Mc" <paulmc@nospam.thehub.com.au> wrote:
>
>G'day Kaz.
>
>Try something like:
>
>select u.room_no, e.stu_no
>from Unit u, Enrolment e
>where (u.unit_code=e.unit_code)
>Group By u.room_no, e.stu_no
>having Count(e.stu_no) >= 4
>order by u.room_no, e.stu_no
>
>Basically this query (In the Group By ... Having clause) counts the number
>of times each stu_no appears next to each disting room_no, and filters the
>results where that count is >= 4.
>
>The syntax I have used is fairly standard, Oracle should handle that no
worries.
>
>
>HTH
>Cheers,
>Paul
>
>>
>>Hi everyone,
>>
>>have yet another query which is proving a bit challenging for my novice
>status
>>- any help would be appreciated, I am using Oracle:
>>
>>Table structure is:
>>
>>Unit Enrolment Student
>>---- --------- -------
>>unit_code unit_code stu_no
>>name stu_no name
>>room_no grade degree
>>
>>The query is:
>>
>>For each room, list all the students who have had at least 4 classes in
>that
>>room.
>>I used: select u.room_no, s.stu_no
>> from u,e,s
>> where (u.unit_code=e.unit_code) and
>> (e.stu_no=s.stu_no)
>> order by u.room_no, s.stu_no
>>
>>and this gave me a list of the rooms and the student enrolments corresponding,
>>but I somehow need to count the times each distinct stu_no appears for
each
>>room_no so I can display those which are >=4. Its this bit I am having
>trouble
>>with.
>>
>>Any ideas???
>>
>>Thanks, Karen
>>
>>
>>
>