[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

Oracle, resultset should return only counts of more than 1

I'm trying to return only rows for people that have a count of more than 1 row with matching criteria. My problem is if I use the 'having count' I cannot display columns that i want to see.
Sample data is below. I do not want to see 123456 data as this person does not have more than 1 row matching criteria:
SQL> select a.person_id, b.class_code, a.school_code, a.end.date
  2  from student_program_class_tracks a, school_classes b
  3  where a.person_id in ('123456','1234567')
  4  and a.school_code = b.school_code
  5  and a.school_year = '20082009'
  6  and a.end_date > '01-JUN-2009'
  7  and a.school_year = b.school_year
  8  and a.class_code = b.class_code
  9  and b.class_homeroom_flag = 'p'
 10  order by 1,2,3,4
 11  /

PERSON_ID     CLASS_CODE       SCHOOL   END_DATE
------------- ---------------- -------- ------------------------
123456    RMA120B          804      26-JUN-09
123456   RMOFFICEB        804      26-JUN-09
1234567   RMA125B          804      26-JUN-09
select a.person_id, b.class_code, a.school_code, a.school_year
from student_program_class_tracks a, school_classes b
where a.school_code = b.school_code
and a.school_year = '20082009'
and a.end_date > '01-JUN-2009'
and a.school_year = b.school_year
and a.class_code = b.class_code
and b.class_homeroom_flag = 'p'
and a.person_id in (select t.person_id
                                               from student_program_class_tracks t
                                               where  t.person_id = a.person_id
                                               and t.school_code = a.school_code
                                               and t.school_year = a.school_year
                                               and t.school_year = '20082009'
                                               and t.end_date > '01-JUN-2009'
                                               group by t.person_id
                                               having count(person_id) > 1)

Open in new window

0
guinnie
Asked:
guinnie
  • 13
  • 10
  • 8
1 Solution
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Assuming personid being the unique identifier...

select distinct A.* from yourtable A join
(select personid, count(personid) from yourtable group by personid having count(personid) > 1) B on A.personid = B.personid
0
 
guinnieAuthor Commented:
Racimo:

  Yes, that works if you only need to see person_id. I need to dsiplay class_code , school_code and school _year also. That's where the difficulty is. For example, I can't use:

group by person_id, class_code, school_code, school_year. If I put those columns in the group by statement, I get no rows returned.

I need to know how to include these columns with the group and having statement.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Same idea...HTH

select distinct a.person_id, b.class_code, a.school_code, a.school_year
from student_program_class_tracks a
        inner join school_classes b
                  on a.school_code = b.school_code
                  and a.school_year = b.school_year
                  and a.class_code = b.class_code
        inner join (select personid, count(distinct personid) from school_classes group by personid having count(distinct personid) > 1) c
                  on a.personid = c. personid
where a.school_year = '20082009'
and a.end_date > '01-JUN-2009'
and b.class_homeroom_flag = 'p'
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
guinnieAuthor Commented:
Racimo:
     That would work but school_classes doesn't have person_id column. I need to match on class_code, school_year, school_code.
SQL> desc school_classes
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SCHOOL_CODE                               NOT NULL VARCHAR2(8)
 SCHOOL_YEAR                               NOT NULL VARCHAR2(8)
 CLASS_CODE                                NOT NULL VARCHAR2(16)


 
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
In that case...

<< That would work but school_classes doesn't have person_id column. I need to match on class_code, school_year, school_code. >>
select distinct a.person_id, b.class_code, a.school_code, a.school_year
from student_program_class_tracks a
        inner join school_classes b
                  on a.school_code = b.school_code
                  and a.school_year = b.school_year
                  and a.class_code = b.class_code
        inner join (select personid, count(distinct personid) from school_classes group by personid having count(distinct personid) > 1) c
                  on a.school_code = c.school_code
                  and a.school_year = c.school_year
                  and a.class_code = c.class_code
where a.school_year = '20082009'
and a.end_date > '01-JUN-2009'
and b.class_homeroom_flag = 'p'
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
I realize I am having some difficulties understanding what you are trying to accomplish.  What is the unique identifier of the student_program_class_tracks table?  Also please try my first proposal...
0
 
guinnieAuthor Commented:
student_program_class_tracks unique identifier is person_id. However, student_program_class_tracks
also has class_code, school_year, school_code which matches to school_classes. But
school classes does not have person_id column.
The 'first proposal' does not work because person_id does not exist in school_classes.


0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<The 'first proposal' does not work because person_id does not exist in school_classes.>>
It's ok...If personid is the unique identifier of student_program_class_tracks then the join on the columns you mentionned does not seem necessary...

Could you try the query thanks...
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
to clarify school classes will return all necessary results...
0
 
guinnieAuthor Commented:
I have pasted result of query below:
SQL> select distinct a.person_id, b.class_code, a.school_code, a.school_year
  2  from student_program_class_tracks a
  3          inner join school_classes b
  4                    on a.school_code = b.school_code
  5                    and a.school_year = b.school_year
  6                    and a.class_code = b.class_code
  7          inner join (select person_id, count(distinct person_id) from school_classes group by pe
rson_id having count(distinct person_id) > 1) c
  8                    on a.school_code = c.school_code
  9                    and a.school_year = c.school_year
 10                    and a.class_code = c.class_code
 11  where a.school_year = '20082009'
 12  and a.end_date > '01-JUN-2009'
 13  and b.class_homeroom_flag = 'p'
 14  /
        inner join (select person_id, count(distinct person_id) from school_classes group by person_id havi
                                                                                                   
ERROR at line 7:
ORA-00904: "PERSON_ID": invalid identifier

0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Sorry please try..

select distinct a.person_id, b.class_code, a.school_code, a.school_year
from student_program_class_tracks a
        inner join school_classes b
                  on a.school_code = b.school_code
                  and a.school_year = b.school_year
                  and a.class_code = b.class_code
        inner join (select personid, count(distinct personid) from student_program_class_tracks group by personid having count(distinct personid) > 1) c
                  on a.personid = c. personid
where a.school_year = '20082009'
and a.end_date > '01-JUN-2009'
and b.class_homeroom_flag = 'p'
0
 
guinnieAuthor Commented:
I think you are close. The only reason we go to school classes is to see if class_code = 'P' . For some reason though it returns no rows. I have paste data to show you what I expect to see. We should see
rows for person_id 0001183676.
PERSON_ID     CLASS_CODE       SCHOOL_C SCHOOL_Y END_DATE
------------- ---------------- -------- -------- ---------
0001186376    RMA120B          804      20082009 26-JUN-09
0001186376    RMOFFICEB        804      20082009 26-JUN-09
This is what I expect to see.
The result of you query is below:
SQL> select distinct a.person_id, b.class_code, a.school_code, a.school_year
  2  from student_program_class_tracks a
  3          inner join school_classes b
  4                    on a.school_code = b.school_code
  5                    and a.school_year = b.school_year
  6                    and a.class_code = b.class_code
  7          inner join (select person_id, count(distinct person_id) from student_program_class_trac
ks group by person_id having count(distinct person_id) > 1) c
  8                    on a.person_id = c. person_id
  9  where a.school_year = '20082009'
 10  and a.end_date > '01-JUN-2009'
 11  and b.class_homeroom_flag = 'p'
 12  /

no rows selected

SQL> select a.person_id, a.class_code, a.school_code, a.school_year, a.end_date
  2  from student_program_class_tracks a
  3  where a.person_id in ('000137546','0001186376')
  4  and a.school_year = '20082009'
  5  and a.end_date > '01-JUN-2009'
  6  order by 1,2,3,4
  7  /

PERSON_ID     CLASS_CODE       SCHOOL_C SCHOOL_Y END_DATE
------------- ---------------- -------- -------- ---------
0001186376    EMS3OV-01        804      20082009 22-JUN-09
0001186376    HRF3O0-03        804      20082009 26-JUN-09
0001186376    IDC3O0-01        804      20082009 22-JUN-09
0001186376    MBF3C1-04        804      20082009 26-JUN-09
0001186376    RMA120B          804      20082009 26-JUN-09
0001186376    RMOFFICEB        804      20082009 26-JUN-09
000137546     CHA3U1-01        804      20082009 26-JUN-09
000137546     HRT3M0-06        804      20082009 26-JUN-09
000137546     MCR3U1-04        804      20082009 26-JUN-09
000137546     RMA125B          804      20082009 26-JUN-09
000137546     SPH3U1-03        804      20082009 26-JUN-09

11 rows selected.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Maybe the collation is case sensitive...

select distinct a.person_id, b.class_code, a.school_code, a.school_year
from student_program_class_tracks a
        inner join school_classes b
                  on a.school_code = b.school_code
                  and a.school_year = b.school_year
                  and a.class_code = b.class_code
        inner join (select personid, count(distinct personid) from student_program_class_tracks group by personid having count(distinct personid) > 1) c
                  on a.personid = c. personid
where a.school_year = '20082009'
and a.end_date > '01-JUN-2009'
and b.class_homeroom_flag = 'P'
0
 
guinnieAuthor Commented:
Tried that too without success. Below is a query, to show data for the 2 people. I only want to dsiplay
person_id, b.class_code, a.school_code, a.school_year for those people who have more than 1 end date being > 01-JUN-2009. But I can't figure it out. The row for person_id 000137546 should not be showing up in final sql.
SQL> select a.person_id, b.class_code, a.school_code, a.school_year, a.end_date
  2  from student_program_class_tracks a, school_classes b
  3  where a.person_id in ('000137546','0001186376')
  4  and a.school_code = b.school_code
  5  and a.school_year = '20082009'
  6  and to_date(a.end_date) > '01-JUN-2009'
  7  and a.school_year = b.school_year
  8  and a.class_code = b.class_code
  9  and b.class_homeroom_flag = 'p'
 10  /

PERSON_ID     CLASS_CODE       SCHOOL_C SCHOOL_Y END_DATE
------------- ---------------- -------- -------- ---------
0001186376    RMA120B          804      20082009 26-JUN-09
0001186376    RMOFFICEB        804      20082009 26-JUN-09
000137546     RMA125B          804      20082009 26-JUN-09

0
 
awking00Commented:
See attached.
query.txt
0
 
awking00Commented:
On further review, I need to remove the class_code (and perhaps the school_code) from the partition clause.
0
 
guinnieAuthor Commented:
Hello Awking:

Thank you for your suggestion. I have ran the query but get error as seen in attached file.
awking-reply.txt
0
 
awking00Commented:
On even further review, I think it just needs to be partitoned by person_id.
0
 
awking00Commented:
Can you post the table structures for the student_program_class_tracks and school_classes tables and provide some sample data for them?
0
 
awking00Commented:
There is a typo, "a.end.date" should be "a.end_date" on the second line.
0
 
guinnieAuthor Commented:
Awking00:

I think you almost have it. The only thing now is I see 1 row returned when I should see both rows.
See attached file. This will show the results of your query and the query that I show the data.
I need to see both rows for person_id 0001186376.
Thank you
awking-reply2.txt
0
 
awking00Commented:
Change "(partition by a.person_id, b.class_code, a.school_code, a.end_date order by rownum)" cnt to "(partition by a.person_id order by rownum) cnt" and see what you get.
0
 
guinnieAuthor Commented:
AWKING00:

That works great. Just one more thing if you don't mind. When I take out test for just 2 person_id, I get too many rows. I only want to get persons with multiple class codes with that end_date. i.e.
I removed check for a.person_id in ('123456','1234567'). Should I put in another inner join to school_program_class_tracks ?
0
 
awking00Commented:
I'm not sure what you mean. Can you post the query without the 2 person_id check, what the results were, and what you expected them to be?
0
 
guinnieAuthor Commented:
Hello Awking:

Sorry for the delay. I will attachequery and results (only parital as the resultset is quite large).
The results are not query with this query. It is returning people with only 1 homeroom. Not sure why .
Hope you can help ?
The 2nd file shows example of people who should not be in resultset.


awking-july14-1.txt
awking-july14-2.txt
0
 
awking00Commented:
See attached.
query.txt
0
 
guinnieAuthor Commented:
Awking00:

Thanks for sticking with this issue. Results are attached.

Also, I will be leaving for the day shortly. But I will be on tonight to respond.
awking-july-15.txt
0
 
awking00Commented:
Can you post the relevant data that exists in the student_program_class_tracks and school_classes tables that provided those latest results?
0
 
guinnieAuthor Commented:
Awking000:

I shall give you a sample. For the data below, only the person id of 0001183676 with class code beginning with RM should be returned. The others do not have more than 1 homeroom.

SQL> select a.person_id, a.class_code, a.school_code, a.school_year, a.end_date
  2  from student_program_class_tracks a
  3  where a.person_id in ('0001100193','0001100019','0001100020','0001186376')
  4  and a.school_year = '20082009'
  5  and a.end_date > '01-JUN-2009'
  6  order by 1,2,3
  7  /

PERSON_ID     CLASS_CODE       SCHOOL_C SCHOOL_Y END_DATE
------------- ---------------- -------- -------- ---------
0001100019    4H               859      20082009 26-JUN-09
0001100020    3K               853      20082009 26-JUN-09
0001100193    5C               829      20082009 26-JUN-09
0001186376    EMS3OV-01        804      20082009 22-JUN-09
0001186376    HRF3O0-03        804      20082009 26-JUN-09
0001186376    IDC3O0-01        804      20082009 22-JUN-09
0001186376    MBF3C1-04        804      20082009 26-JUN-09
0001186376    RMA120B          804      20082009 26-JUN-09
0001186376    RMOFFICEB        804      20082009 26-JUN-09

9 rows selected.
0
 
awking00Commented:
Okay, now I'm a little confused. When you ran the last query, we got results like the following:
0001100019    4H               859      26-JUN-09          1
0001100019    4H               859      26-JUN-09          2
0001100020    3K               853      26-JUN-09          1
0001100020    3K               853      26-JUN-09          2
0001100193    5C               829      26-JUN-09          1
0001100193    5C               829      26-JUN-09          2
0001100193    5C               829      26-JUN-09          3
0001100193    5C               829      26-JUN-09          4
Indicating that there were duplicate values for the four attributes, but now you're not showing them.
Also, what do you mean by only the 0001186376 recode with class_code beginning with RM should be returned? If the values were just
0001186376    EMS3OV-01        804      20082009 22-JUN-09
0001186376    HRF3O0-03        804      20082009 26-JUN-09
0001186376    IDC3O0-01        804      20082009 22-JUN-09
0001186376    MBF3C1-04        804      20082009 26-JUN-09
0001186376    RMA120B          804      20082009 26-JUN-09
without the RMOFFICEB record, should the id be returned or not? If not, what is the criteria you would use to eliminate the other four records with class_codes that don't begin with RM?
0
 
guinnieAuthor Commented:
Awking00:

 I'm only showing examples because the result sets can be very large. What I'm trying to say is this:
Certain all person_ids will have multiple class_codes, it is only when we go to the school_classes
table and look for those class codes that are flagged as 'P' that we know that it is a homeroom.
In the case of person_id 0001186376, the class codes that are RMA120b and RMOFFICEB are flagged as 'P' in the school_classes so we want to see them in the resultset .
Does that clarify ?
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 13
  • 10
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now