PL/SQL count inside cursor loop

[CBK]

My query uses a union to select two fields from table stu. The two fields are stu_id, class. My goal is to list classes from both semester 1 (S1) and semester 2 (S2) without duplicating classes that were taken in both S1 and S2 (hence the union).

I then want a count of the number of classes each student has taken. If I use count in my query I end up with an inaccurate number, i.e. 5 classes in S1 and 6 in S2. So I want to place query in cursor then count those results to determine number of classes for each student.

I have created a cursor get_stu_count and want to do the following:

for x in get_stu_count
loop
-- now here is where I cannot figure out
-- how to keep track of individual stu_ids
-- when processing counter


Any help would be greatly appreciated

 

[sem]

I can not understand what your cursor is for, but may suggest to

select count(*) from (select ... union select ...)

You may also consider grouping by stu_id.

Regards, Dima

http://seminihin.narod.ru

 

[CBK]

Thanks Sem, nothing like keeping it simple

 

[nagornyi]

select stu_id, count(class_id) from
(select distinct stu_id, class_id from table where semester in ('S1', 'S2'))
group by stU_id