Home » SQL & PL/SQL » SQL & PL/SQL » Get details of college leavers
Get details of college leavers [message #269007] Thu, 20 September 2007 07:46 Go to next message
hermiod
Messages: 7
Registered: September 2007
Location: UK
Junior Member
Hi All

I work for a college and am building a new report suite which encompasses all our old reports aswell as some new ones.

One of the new reports I am trying to make lists all learners who have left every course they are enrolled on for a given academic period.

The table the details are in follows this format

stuaos:
Student_id    aos_code  aos_period  stage_code   acad_period
PAR01234567   SCO012       41G        WDN          07/08
BOB01234687   SOC001       13F        ENR          07/08
PAR01234567   SCO013       41E        NAT          07/08
PAR01234567   SOC011       15D        TRN          07/08
BOB01234687   SOC002       01K        WDN          07/08


In the case of this example, the query would return PAR01234567 because that learner has left all their courses (WDN, TRN, NAT) but it would not return BOB01234687 because they are still enrolled on a course(ENR)

I was given an SQL query from a guy who has made alot of the old reports but that query doesn't work and this is quite a bit more advanced than I have worked on before so I don't really have a clue where to start.

Here is the query I was given:-

SELECT DISTINCT srdept.dept_code A,   srdept.description B,
   COUNT(DISTINCT stbiog.student_id) C, stuaos.acad_period D   
FROM stsess,stuaos,srdept,stbiog   
WHERE stsess.aos_code = stuaos.aos_code   
AND stsess.aos_period = stuaos.aos_period   
AND stsess.acad_period = stuaos.acad_period   
AND stsess.dept_code = srdept.dept_code   
AND stuaos.student_id = stbiog.student_id   
AND stsess.acad_period = '07/08'   
AND stuaos.stage_ind = 'E'   
AND stuaos.student_id in   (SELECT enr1.student_id   
FROM stuaos enr1   
WHERE enr1.acad_period = '07/08'   
AND enr1.stage_ind = 'E'   
AND enr1.stage_code in ('WDN','TRN','NAT')   
GROUP BY enr1.student_id   having COUNT(enr1.student_id) in   (SELECT COUNT(enr2.student_id)  
FROM stuaos enr2  
WHERE enr2.acad_period = '07/08'   
AND enr2.stage_ind = 'E'   
AND enr1.student_id = enr2.student_id   
GROUP BY enr2.student_id))   
GROUP BY srdept.dept_code,srdept.description,stuaos.acad_period


Any help that you guys could provide would be greatly appreciated, even if you can just steer me in the right direction of where to go with this.

[Updated on: Thu, 20 September 2007 08:07] by Moderator

Report message to a moderator

Re: Get details of college leavers [message #269023 is a reply to message #269007] Thu, 20 September 2007 08:35 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Something like this ?
SQL> create table marc_test (student_id varchar2(10), stage_code varchar2(3));

Table created.

SQL> insert into marc_test values('1','WDN');

1 row created.

SQL> insert into marc_test values('2','WDN');

1 row created.

SQL> insert into marc_test values('2','WDN');

1 row created.

SQL> insert into marc_test values('1','ENR');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from marc_test;

STUDENT_ID STA
---------- ---
1          WDN
2          WDN
2          WDN
1          ENR

SQL> select count(*),student_id from marc_test a 
     having count(*) = 
    (select count(*) from marc_test where 
     stage_code != 'ENR' and student_id = a.student_id)
    group by student_id
    ;

  COUNT(*) STUDENT_ID
---------- ----------
         2 2

 

Just a quick shot

[Updated on: Thu, 20 September 2007 08:35]

Report message to a moderator

Re: Get details of college leavers [message #269038 is a reply to message #269023] Thu, 20 September 2007 09:10 Go to previous messageGo to next message
hermiod
Messages: 7
Registered: September 2007
Location: UK
Junior Member
I've tweaked it a little as there were some other stage codes i`d forgotten about and added a reference to stage_ind so it excludes anyone on a waiting list who hasn't actually enrolled yet, and it works an absolute charm.

select count(*), student_id from stmaos a having count(*) = 
    (select count(*) 
     from stmaos 
     where stage_code in ('WDN', 'TRN', 'NAT')  
     and student_id = a.student_id
     and acad_period='07/08'
     and stage_ind='E')
     GROUP BY student_id


I've also been asked to compile a separate leaver report which breaks down the number of leavers by the department.

The table structure in my first post also contains another field called dept_code. Is it possible to adapt this query to bring out a list of departments that have leavers along with a count of the number of leavers.

I've tried the following but get an 00936 - missing expression error:

select count(*), distinct dept_code from stmaos a having count(*) = 
    (select count(*) 
     from stmaos 
     where stage_code in ('WDN', 'TRN', 'NAT')  
     and student_id = a.student_id
     and acad_period='07/08'
     and stage_ind='E')
     GROUP BY distinct dept_code


The first select line doesn't look right to me but I seem to be undergoing a bit of a brain freeze at the moment!!!
Re: Get details of college leavers [message #269069 is a reply to message #269007] Thu, 20 September 2007 11:48 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Try taking the distinct out, that's what group by does.
Re: Get details of college leavers [message #269204 is a reply to message #269069] Fri, 21 September 2007 02:44 Go to previous messageGo to next message
hermiod
Messages: 7
Registered: September 2007
Location: UK
Junior Member
Tried taking the distinct out and adding dept_code in place of student_id but still no luck.

I`ll explain what this new query needs to do just incase there is a different way of doing it.

stmaos:
Student_id  aos_code aos_period stage_code acad_period dept_code
PAR01234567 SCO012      41G       WDN        07/08       DEP1
BOB01234687 SOC001      13F       ENR        07/08       DEP2
PAR01234567 SCO013      41E       NAT        07/08       DEP1
PAR01234567 SOC011      15D       TRN        07/08       DEP3
BOB01234687 SOC002      01K       WDN        07/08       DEP2


Using this example, PAR01234567 would show as a leaver for both DEP1 and DEP3 because he has left all courses in those departments. BOB01234687 wouldn't show as a leaver for DEP2 because he is still enrolled on one course in that department.

So whereas the original query showed learners who had left all courses within the college, this one wants to show learners who have left every course within a specific department that they were enrolled on.

This is what I've got...

1. SELECT COUNT(*), dept_code FROM stmaos a having COUNT(*) = 
2.     (SELECT COUNT(*) 
3.      FROM stmaos 
4.      WHERE stage_code in ('WDN', 'TRN', 'NAT')  
5.      AND student_id = a.student_id
6.      AND acad_period = a.acad_period
7.      AND a.acad_period='07/08'
8.      AND stage_ind='E')
9. GROUP BY dept_code


I get an 'ORA-00979: not a GROUP BY expression' error. There is only one retrieved field and this is in the GROUP BY so I`m not sure where it is failing here.
One problem I had with the query you gave me before was that it pulled in learners from previous academic periods rather than just the current, so I've added line 6 to prevent this.

I hope I don't sound ungrateful in asking you to help me change the query to another purpose but I really don't know what I'm doing here!
Re: Get details of college leavers [message #269297 is a reply to message #269007] Fri, 21 September 2007 09:45 Go to previous message
hermiod
Messages: 7
Registered: September 2007
Location: UK
Junior Member
I've spent the best part of the afternoon working with our Systems Manager who knows waaaaay more about SQL and Oracle than myself and we've managed to get a working query going.

Here it is for anyone who faces a situation which requires a similar query.

SELECT stmaos.dept_code,Count(DISTINCT student_id) numbers, strdeprt.description
FROM stmaos, strdeprt
WHERE stmaos.dept_code = strdeprt.dept_code
AND student_id in
(select enr1.student_id
from stmaos enr1
where enr1.acad_period = '07/08'
and enr1.stage_ind = 'E'
and enr1.stage_code in ('WDN','TRN','NAT')
group by enr1.student_id
having count(enr1.student_id) in
(select count(enr2.student_id)
from stmaos enr2
where enr2.acad_period = '07/08'
and enr2.stage_ind = 'E'
and enr1.student_id = enr2.student_id
group by enr2.student_id))
GROUP BY acad_period, stmaos.dept_code, strdeprt.description
HAVING acad_period = '07/08'


Thanks for your help MarcL, it is greatly appreciated.
Previous Topic: query explanation
Next Topic: Need Help with Query to break down a combination
Goto Forum:
  


Current Time: Fri Dec 02 14:36:21 CST 2016

Total time taken to generate the page: 0.13041 seconds